topic Re: Converting to a crosstable in QlikView

Converting to a crosstable

QlikMo — Wed, 01 Dec 2021 13:38:46 GMT

Hello,

I need some help converting my data to a cross table format.

I have a data source with a list of prices. For each supplier / item combination I need to create a row with the last five prices as columns

This is the example source data

LOAD * INLINE [ Item, EffectiveDate, Price, Supplier Screw001, 15/11/2021, 500, S00100 Screw001, 01/06/2021, 480, S00100 Screw001, 02/04/2021, 475, S00100 Screw001, 01/01/2021, 470, S00100 Nut001, 10/10/2021, 200, S00352 Nut001, 01/10/2021, 199, S00352 Nut001, 02/05/2020, 170, S00352 Bolt001, 16/09/2021, 15, S00421 Bolt001, 14/02/2021, 14, S00421 Bolt001, 01/02/2021, 14, S00421 Bolt001, 01/12/2020, 12, S00421 Bolt001, 06/06/2020, 10, S00421 Bolt001, 01/01/2020, 5, S00421 Screw001, 01/11/2020, 450, S00200 ];

This would be the expected output.

%ItemSupplierKey	Current Price	Current Effective Date	Previous Price	Previous Effective Date	Third Price	Third Effective Date	Fourth Price	Fourth Effective Date	Fifth Price	Fifth Effective Date
Screw001\|S00100	500	15/11/2021	480	01/06/2021	475	02/04/2021	470	01/01/2021
Nut001\|S00352	200	10/10/2021	199	01/10/2021	170	02/05/2020
Bolt001\|S00421	15	16/09/2021	14	14/02/2021	14	01/02/2021	12	01/12/2020	10	06/06/2020
Screw001\|S00200	450	01/11/2020

Notes - only the five most recent prices should be used (EffectiveDate) by Item and Supplier. Bolt001 has 6 prices so the last one should not be shown

Also an item can have multiple suppliers e.g. Item Screw001. These should show as separate rows

I would appreciate any help on this. I've not done this before in Qlikview. Normally I use the crosstable function to covert these types of tables to a row format. I started off with loops but this took a long time in the load script. I then started looking at generic load but I am struggling with that too. I would really appreciate any tips or input

Thank you

Re: Converting to a crosstable

QlikMo — Wed, 01 Dec 2021 14:25:31 GMT

I think progress has been made using the Autonumber function twice. I just need to figure out how to get the rows to merge rather than a new row and then I am pretty much there.

I am not sure if this is the most efficient method so I welcome any feedback.

Thanks

Prices: LOAD * INLINE [ Item, EffectiveDate, Price, Supplier Screw001, 15/11/2021, 500, S00100 Screw001, 01/06/2021, 480, S00100 Screw001, 02/04/2021, 475, S00100 Screw001, 01/01/2021, 470, S00100 Nut001, 10/10/2021, 200, S00352 Nut001, 01/10/2021, 199, S00352 Nut001, 02/05/2020, 170, S00352 Bolt001, 16/09/2021, 15, S00421 Bolt001, 14/02/2021, 14, S00421 Bolt001, 01/02/2021, 14, S00421 Bolt001, 01/12/2020, 12, S00421 Bolt001, 06/06/2020, 10, S00421 Bolt001, 01/01/2020, 5, S00421 Screw001, 01/11/2020, 450, S00200 ]; New: LOAD *, (Item &'|' & Supplier) as %ItemSupplierKey, autonumber(Item &'|' & Supplier) as ID RESIDENT Prices order by Item, Supplier, EffectiveDate DESC; drop table Prices; New2: LOAD *, Autonumber(RowNo(),ID) as ID2 RESIDENT New; drop table New; NoConcatenate New3: LOAD * RESIDENT New2 where ID2 <= '5'; Drop table New2; Final: LOAD %ItemSupplierKey, if(ID2 = 1, Price) as [Current Price], if(ID2 = 2, Price) as [Previous Price], if(ID2 = 3, Price) as [Third Price], if(ID2 = 4, Price) as [Fourth Price], if(ID2 = 5, Price) as [Fifth Price] RESIDENT New3;

Re: Converting to a crosstable

QlikMo — Wed, 01 Dec 2021 17:51:55 GMT

Could anyone help me with the 'Final' section in the previously posted code?

I am struggling to merge the rows when the %ItemSupplierKey is the same. I have tried quite a few things but no luck.

I need to convert it from this

to this

I've tried things below

Final: LOAD %ItemSupplierKey, if(ID2 = 1, Price) as [Current Price] RESIDENT New3; LOAD %ItemSupplierKey, if(ID2 = 2, Price) as [Previous Price] RESIDENT New3 where len(Price) > 0; LOAD %ItemSupplierKey, if(ID2 = 3, Price) as [Third Price] RESIDENT New3 where len(Price) > 0; LOAD %ItemSupplierKey, if(ID2 = 4, Price) as [Fourth Price] RESIDENT New3 where len(Price) > 0; LOAD %ItemSupplierKey, if(ID2 = 5, Price) as [Fifth Price] RESIDENT New3 where len(Price) > 0;

and also using joins, concatenates etc. with no luck. I also thought about adding the null columns in each load in the hope it joins

Final: LOAD %ItemSupplierKey, if(ID2 = 1, Price) as [Current Price], null() as [Previous Price], null() as [Third Price], null() as [Fourth Price], null() as [Fifth Price] RESIDENT New3; //Concatenate //join (Final) inner join (Final) LOAD %ItemSupplierKey, if(ID2 = 2, Price) as [Previous Price], null() as [Third Price], null() as [Fourth Price], null() as [Fifth Price], null()as [Current Price] RESIDENT New3 where len(Price) > 0; //Concatenate //join (Final) inner join (Final) LOAD %ItemSupplierKey, if(ID2 = 3, Price) as [Third Price], null() as [Fourth Price], null() as [Fifth Price], null() as [Current Price], null() as [Previous Price] RESIDENT New3 where len(Price) > 0; //Concatenate //join (Final) inner join (Final) LOAD %ItemSupplierKey, if(ID2 = 4, Price) as [Fourth Price], null() as [Fifth Price], null() as [Current Price], null() as [Previous Price], null() as [Third Price] RESIDENT New3 where len(Price) > 0; //Concatenate //join (Final) inner join (Final) LOAD %ItemSupplierKey, if(ID2 = 5, Price) as [Fifth Price], null() as [Current Price], null() as [Previous Price], null() as [Third Price], null() as [Fourth Price] RESIDENT New3 where len(Price) > 0;

Re: Converting to a crosstable

MarcoWedel — Wed, 01 Dec 2021 22:29:32 GMT

Hi,

one solution using a generic load like you initially seem to have tried as well:

table1: LOAD Item&'|'&Supplier as %ItemSupplierKey, EffectiveDate, Price INLINE [ Item, EffectiveDate, Price, Supplier Screw001, 15/11/2021, 500, S00100 Screw001, 01/06/2021, 480, S00100 Screw001, 02/04/2021, 475, S00100 Screw001, 01/01/2021, 470, S00100 Nut001, 10/10/2021, 200, S00352 Nut001, 01/10/2021, 199, S00352 Nut001, 02/05/2020, 170, S00352 Bolt001, 16/09/2021, 15, S00421 Bolt001, 14/02/2021, 14, S00421 Bolt001, 01/02/2021, 14, S00421 Bolt001, 01/12/2020, 12, S00421 Bolt001, 06/06/2020, 10, S00421 Bolt001, 01/01/2020, 5, S00421 Screw001, 01/11/2020, 450, S00200 ]; table2: LOAD *, Pick(AutoNumber(EffectiveDate,%ItemSupplierKey),'Current','Previous','Third','Fourth','Fifth') as Sequence Resident table1 Where AutoNumber(EffectiveDate,%ItemSupplierKey)<6 Order By EffectiveDate Desc; tabPrice: Generic LOAD %ItemSupplierKey, Sequence&' Price', Price Resident table2; tabEffDate: Generic LOAD %ItemSupplierKey, Sequence&' Effective Date', EffectiveDate Resident table2; DROP Tables table1, table2; tabFinal: LOAD '' as TempField AutoGenerate 0; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)','tabPrice.*','tabEffDate.*') THEN Join (tabFinal) LOAD * Resident [$(vTable)]; DROP Table [$(vTable)]; ENDIF NEXT i DROP Field TempField;

hope this helps

Marco

Re: Converting to a crosstable

QlikMo — Thu, 02 Dec 2021 09:07:06 GMT

Hi Marco,

Thank you, really appreciate that