Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
geogou1973
New Contributor II

Cross Table

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

    

1 Solution

Accepted Solutions
Partner
Partner

Re: Cross Table

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

Capture.PNG

View solution in original post

5 Replies

Re: Cross Table

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

geogou1973
New Contributor II

Re: Cross Table

 

I gave an example only 

Re: Cross Table

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
New Contributor II

Re: Cross Table

yes

Partner
Partner

Re: Cross Table

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

Capture.PNG

View solution in original post