topic Re: Cross Table in QlikView

Cross Table

geogou1973 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cross Table

sunny_talwar — Fri, 21 Dec 2018 15:27:01 GMT

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

Re: Cross Table

geogou1973 — Fri, 21 Dec 2018 15:48:12 GMT

I gave an example only

Re: Cross Table

sunny_talwar — Fri, 21 Dec 2018 15:54:56 GMT

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

Re: Cross Table

geogou1973 — Fri, 21 Dec 2018 15:56:05 GMT

yes

Re: Cross Table

dplr-rn — Fri, 21 Dec 2018 18:59:29 GMT

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