Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
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.
Do you need results as below?
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
;
How can I group on expressions in QLIKVIEW
See attached for sample report. We need all data for each item in line up in a single row
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.