Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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;
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
Hi Marco,
Thank you, really appreciate that