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

[maepana]

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...

[Taoufiq_Zarra]

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

2

CCC

DDD,AAA

10

10

<- Summarized header 300 info

[Kushal_Chawda]

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

[Taoufiq_Zarra]

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:

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output:

[Brett_Bleess]

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