Cross Table

[geogou1973]

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

[sunny_talwar]

How are you getting 50, 100, 70 as qty? I see 4, 4, 4 for those three days in the Excel file

[geogou1973]

I gave an example only 

[sunny_talwar]

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

[geogou1973]

yes

[dplr-rn · Accepted solution]

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