topic Re: Merge Multiple rows for each item into one row in Reporting Service & Alerting

Merge Multiple rows for each item into one row

akrueger — Fri, 15 Nov 2024 22:17:31 GMT

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

Re: Merge Multiple rows for each item into one row

Digvijay_Singh — Wed, 16 Mar 2022 23:42:18 GMT

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?

Re: Merge Multiple rows for each item into one row

akrueger — Thu, 17 Mar 2022 16:12:43 GMT

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.

Re: Merge Multiple rows for each item into one row

Digvijay_Singh — Fri, 18 Mar 2022 00:05:43 GMT

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
;

Re: Merge Multiple rows for each item into one row

akrueger — Mon, 21 Mar 2022 23:30:10 GMT

How can I group on expressions in QLIKVIEW

Re: Merge Multiple rows for each item into one row

akrueger — Tue, 22 Mar 2022 20:36:19 GMT

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

Re: Merge Multiple rows for each item into one row

Digvijay_Singh — Tue, 22 Mar 2022 21:14:31 GMT

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.