Converting to a crosstable

[QlikMo]

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

[QlikMo]

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;

[QlikMo]

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;

[MarcoWedel · Accepted solution]

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

[QlikMo]

Hi Marco,

Thank you, really appreciate that