Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Can anyone help me to find a solution how to transform a file with cross table ?
In the attached file i have the store, the item and the fields of qty from 1 to 31 that is the qty per day of the current month.
How can i change is to store, item, (date that is all the fields of the qty), qty
021, 1000006, 1 , 50
021, 1000006, 2 , 100
021, 1000006, 3, 70
and so on
Thank you in advance
See below
QuantTrackingTemp: CrossTable (ProductTemp, Quantity,2) LOAD STOR3, ITEM, QST1, QST2, QST3, QST4, QST5, QST6, QST7, QST8, QST9, QST10, QST11, QST12, QST13, QST14, QST15, QST16, QST17, QST18, QST19, QST20, QST21, QST22, QST23, QST24, QST25, QST26, QST27, QST28, QST29, QST30, QST31 FROM [lib://Downloads/CROS_TABLE.XLS] (biff, embedded labels, table is CROS_TABLE$); QuantTracking: load STOR3, ITEM, PurgeChar(ProductTemp,'QST') as Product, Quantity resident QuantTrackingTemp ; drop table QuantTrackingTemp;
result
How are you getting 50, 100, 70 as qty? I see 4, 4, 4 for those three days in the Excel file
I gave an example only
So, the output based on your sample data would look like this?
021, 1000006, 1 , 4
021, 1000006, 2 , 4
021, 1000006, 3, 4
... and so on...
yes
See below
QuantTrackingTemp: CrossTable (ProductTemp, Quantity,2) LOAD STOR3, ITEM, QST1, QST2, QST3, QST4, QST5, QST6, QST7, QST8, QST9, QST10, QST11, QST12, QST13, QST14, QST15, QST16, QST17, QST18, QST19, QST20, QST21, QST22, QST23, QST24, QST25, QST26, QST27, QST28, QST29, QST30, QST31 FROM [lib://Downloads/CROS_TABLE.XLS] (biff, embedded labels, table is CROS_TABLE$); QuantTracking: load STOR3, ITEM, PurgeChar(ProductTemp,'QST') as Product, Quantity resident QuantTrackingTemp ; drop table QuantTrackingTemp;
result