Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
akrueger
Contributor
Contributor

Merge Multiple rows for each item into one row

I have multiple rows for each item. I want to merge them so everything for that item is on the same row. See example. I want the Last Received Date and Last Sale date to be on the same line for that item number

 

Item A Description A 12-12-2019   04-17-2020(sales Date A
Item A Description A 12-12-2019 04-16-2020(Received Date A  
Item B Description B 04-26-2020   06-24-2021
Item B Description B 04-26-2020 06-23-21  
Item B Description B 04-26-2020 07-24-21  
Item C Description C 03-31-2021   08-09-2020
Labels (1)
6 Replies
Digvijay_Singh

What if you have multiple different receive date for an item. I see item B got 6/23 and 7/24 receive date and the top row for Item B doesn't have a receive date, how do you want to merge, these three different values in one cell? 

akrueger
Contributor
Contributor
Author

Yes, we do have multiple received dates sometimes because item might have more than one receipt transaction. We want to get the max received date and max sales date on the same line for the one item.

Digvijay_Singh

Do you need results as below?

Digvijay_Singh_0-1647561920549.png

Used below script - 

Source:
Load Item,Description,
Date#([Date1],'MM-DD-YYYY') as [Date],
Date#([Receive Date1],'MM-DD-YYYY') as [Receive Date],
Date#([Sales Date1],'MM-DD-YYYY') as [Sales Date]
inline [
Item, Description, Date1, Receive Date1, Sales Date1
Item A, Description A, 12-12-2019,,04-17-2020
Item A, Description A, 12-12-2019,04-16-2020
Item B, Description B, 04-26-2020,,06-24-2021
Item B, Description B, 04-26-2020,06-23-21
Item B, Description B, 04-26-2020,07-24-21
Item C, Description C, 03-31-2021,,08-09-2020
];

NoConcatenate
Final:
Load
Item,
Description,
Date,
Date(Max([Receive Date])) as [Receive Date],
Date(Max([Sales Date])) as [Sales Date]
resident Source
Group By Item, Description,Date
;

Drop table Source
;

akrueger
Contributor
Contributor
Author

How can I group on expressions in QLIKVIEW

akrueger
Contributor
Contributor
Author

akrueger_1-1647981103376.png

See attached for sample report. We need all data for each item in line up in a single row

Digvijay_Singh

Do you mean you want to do it in the chart instead of script?

In your screenshot what if you have just two measures created for last two columns and rest of them as dimensions?

In the measures you can have expression Max(Date) to show single date.

Not sure if I understand things correctly but if all the dimension values are same for multiple rows you can merge them in one row by using max(Date) as your measure.