Eclipse Community Forums: BIRT » Duplicate rows in Joint Dataset

Help

Home

Home » Archived » BIRT » Duplicate rows in Joint Dataset(Getting duplicate rows in Joint DataSet)

Show: Today's Messages :: Show Polls :: Message Navigator

Duplicate rows in Joint Dataset [message #1727758]

Sat, 26 March 2016 22:59

JP Redro

Messages: 181
Registered: July 2009
Location: India

Senior Member

Hi Experts,

I am using BIRT 4.3.1 and DatSource is XML file. As per our requirement i am joining two datastes using left outer join, while fetching data using Joint dataset, i am getting duplicate rows when the invoice have two payments. Meaning
Invoice number is 1234 then the corresponding two payments have same invoice number like 1234. When i am joining two datasets (Invoices and payments) i am getting invoice is twice. Please suggest me how to remove that duplicate rows. Also attached is the sample report and xml source file.
index.php/fa/25432/0/

Attachment: 0148.gif
(Size: 4.76KB, Downloaded 1236 times)
Attachment: 531004_6.xml
(Size: 34.34KB, Downloaded 118 times)
Attachment: new_report_6.rptdesign
(Size: 111.72KB, Downloaded 313 times)

Thanks,
JP

[Updated on: Sat, 26 March 2016 23:00]

Report message to a moderator

Re: Duplicate rows in Joint Dataset [message #1727868 is a reply to message #1727758]

Mon, 28 March 2016 17:34

Jerry Sheets

Messages: 76
Registered: July 2009
Location: Salt Lake City, UT

Member

You are getting the duplicates because the tables are only joined on invoice number. (I haven't used XML before and I don't see how to join on more than one one column, so there may be a better way to do this.) Try the attached and see if it does what you want. I just created a group based on concatenating INV_NUM and TRX_DATE then moved the columns from the detail line to the group. If you ever have more than one payment per day on an invoice, you might need to include the reference or some other column(s) to make the group unique.

Hope this helps.