topic Re: Load script: How to rearrange and group header and line items in QlikView

Load script: How to rearrange and group header and line items

maepana — Sat, 16 Nov 2024 18:23:22 GMT

Hi experts,

Could you advice, how to right load script for below case. I have tried but failed...

Source data:

Header	Lineitem	Up or Down	Price
100	AAA	U	$20
100	BBB	D	$20
200	AAA	U	$30
200	BBB	D	$30
300	CCC	U	$10
300	DDD	D	$5
300	AAA	D	$5

Output1:

Grouping based on [Up or Down] and [Lineitem] pattern of [Header], sum by [Price]

Shape No.	Up lineitem	Down lineitem	Total Up Price	Total Down Price
1	AAA	BBB	50	50	<- Summarized header 100 and 200 info
2	CCC	DDD,AAA	10	10	<- Summarized header 300 info

Output2:

Link table of [Header] and [Shapre No.]

Header	Shape No.
100	1
200	1
300	2

Not sure if this is possible in Qlikview...

Re: Load script: How to rearrange and group header and line items

Taoufiq_Zarra — Fri, 10 Jul 2020 09:04:48 GMT

what I didn't get , how you group DDD,AAA ?

CCC

DDD,AAA

<- Summarized header 300 info

Re: Load script: How to rearrange and group header and line items

Kushal_Chawda — Fri, 10 Jul 2020 10:22:08 GMT

How do you define shape no? Seems confusing. Can you elaborate?

Re: Load script: How to rearrange and group header and line items

Taoufiq_Zarra — Fri, 10 Jul 2020 10:52:16 GMT

If I understood correctly, you need this :

Data: LOAD if(Header=300,2,if(Header=200 or Header=100,1)) as [Shape No.],if([Up or Down]='D',Lineitem) as [Down lineitem],if([Up or Down]='U',Lineitem) as [Up lineitem], if([Up or Down]='D',Price) as [Total Down Price],if([Up or Down]='U',Price) as [Total Up Price], * INLINE [ Header, Lineitem, Up or Down, Price 100, AAA, U, 20 100, BBB, D, 20 200, AAA, U, 30 200, BBB, D, 30 300, CCC, U, 10 300, DDD, D, 5 300, AAA, D, 5 ]; output2: load distinct Header,[Shape No.] resident Data; output1: load [Shape No.],concat(distinct [Up lineitem],',') as [Up lineitem],concat(distinct [Down lineitem],',') as [Down lineitem], sum([Total Up Price]) as [Total Up Price], sum([Total Down Price]) as [Total Down Price] resident Data group by [Shape No.]; drop table Data;

output :

output1:

output2:

Re: Load script: How to rearrange and group header and line items

Saravanan_Desingh — Fri, 10 Jul 2020 13:47:28 GMT

One solution is.

tab1: LOAD * INLINE [ Header, Lineitem, Up or Down, Price 100, AAA, U, 20 100, BBB, D, 20 200, AAA, U, 30 200, BBB, D, 30 300, CCC, U, 10 300, DDD, D, 5 300, AAA, D, 5 ]; Left Join(tab1) LOAD Header, Concat(Lineitem,'',[Up or Down]='U') As Pattern Resident tab1 Group By Header ; Left Join(tab1) LOAD *, AutoNumber(Pattern) As [Shape No.] Resident tab1;

Re: Load script: How to rearrange and group header and line items

Saravanan_Desingh — Fri, 10 Jul 2020 13:49:04 GMT

Output:

Re: Load script: How to rearrange and group header and line items

Brett_Bleess — Thu, 23 Jul 2020 12:34:35 GMT

You have received multiple posts to your thread, we would greatly appreciate it if you would return to the thread and close it out by using the Accept as Solution button on the post(s) that helped, as this gives the poster(s) credit for the help and lets other Members know what worked. If you did something different, you can post what you did and then mark that, but if any of the other posts gave you ideas, I would use the Like indicator on those posts to give them some credit for the help too, and if you have further questions, please leave an update post.

Regards,
Brett