Getting duplicate rows on the left in Birt reports

Question

I am connecting two datasets in Birt. His left outer connection. Below is a screenshot of the datasets.

The reason I need all the rows of the left table, I do some timestamp calculations for all the rows of the left table. I need to calculate the priority levels (how many times this happened) in the right table, if the terminal identifier matches the left table.

When I get records, it gets duplicate records, which cause my timestamp calculations to double. I can’t do the inner join because I need to do the timestamp calculation from the left table for the required.

The ratio of both tables from many to many. I will explain with an example what the problem I am facing is and what I want to achieve. For instance. This is the data for DeviceEventObject dataset events:

record 1:

"event" : "EXITED SUPERVISOR MODE", "timestamp" : ISODate("2017-12-17T06:06:23.181Z"), "terminal" : { "terminalId" : "testterminal", "branchId" : "test" } 

record 2:

  "event" : "ENTERED SUPERVISOR MODE", "timestamp" : ISODate("2017-12-17T06:06:23.181Z"), "terminal" : { "terminalId" : "testterminal", "branchId" : "test" } 

From this timestamp of each event, I calculate the time between input and output events.

Now another data set is DeviceStatusErrorCodePrioirtyLevel: For example. These are the entries in this dataset:

 "status" : "Online", "errorCode" : "123", "priorityLevel" : "test", "emailTypeCode" : "123", "terminal" : { "terminalId" : "testterminal", "branchId" : "test" } 

Now I want to calculate the number of times that the “test” of the priority level for the “testterminal” terminal was installed. with the above dataset number will be 1. I am connecting both datasets based on terminalId.

Now, with the dataset above, I get duplicate records that double my time that I am counting, and also get the score for priority level 2. For example, this is what I get:

 "event" : "EXITED SUPERVISOR MODE", "priorityLevel" : "test" "event" : "ENTERED SUPERVISOR MODE", "priorityLevel" : "test" 

I want to:

  "event" : "EXITED SUPERVISOR MODE", "priorityLevel" : "test" "event" : "ENTERED SUPERVISOR MODE", 

Additional information about the birt project:

Sample data from both datasets:

 DeviceStatusErrorCodePrioirtyLevel: { "_id" : ObjectId("5a36095f1854ad0b7096184b"), "className" : "com.omnia.pie.cm.models.snapshot.terminal.v2.DeviceStatusErrorCodePrioirtyLevel", "timestamp" : ISODate("2017-12-17T06:06:23.181Z"), "deviceName" : "CardReader", "status" : "Online", "errorCode" : "123", "priorityLevel" : "test", "emailTypeCode" : "123", "terminal" : { "terminalId" : "testterminal", "branchId" : "test" } } DeviceEventObject: { "_id" : ObjectId("5a3608c61854ad0b70961846"), "className" : "com.omnia.pie.cm.models.snapshot.terminal.v2.DeviceEventObject", "event" : "EXITED SUPERVISOR MODE", "value" : "True", "timestamp" : ISODate("2017-12-17T06:03:50.901Z"), "transactionData" : { "transactionType" : "", "transactionNumber" : "", "sessionId" : "" }, "terminal" : { "terminalId" : "testterminal", "branchId" : "test" } } 

Here is the link to my report in case: https://drive.google.com/file/d/1dHOEneG2-fbeP9Mz86LUhuk0tSxnLZxi/view?usp=sharing

Answer 1

Add New Dataset for DeviceEventObject

Add the following aggregate function to the command expression aggregator.

The following is the $lookup function of the data from the priority level of the status error code based on terminalId, followed by $unwind to smooth the data.

$group smooth data on the terminal to accumulate separate priority levels for the terminal identifier.

$project to count different priority levels

 [{$lookup:{ from: "devicestatuserrorcodeprioirtylevel", // name of the collection localField: "terminal.terminalId", foreignField: "terminal.terminalId", as: "dsecpl" }}, {$unwind:"$dsecpl"}, {$group:{ "_id":"$terminal.terminalId", "prioritylevels":{"$addToSet":"$dsecpl.priorityLevel"}, "events":{"$push":"$event"} }}, {"$project":{"prioritylevelcount":{"$size":"$prioritylevels"}, "events": 1} } ] 

Move all available fields to the selected field column.

Preview the results.