topic Re: Transforming table before loading in QlikView

Transforming table before loading

Anonymous — Mon, 05 Mar 2018 14:54:57 GMT

Hi, I'm struggling to transpose a table before loading in it to Qlik Sense.

What I'm trying to achieve is, transform the following table:

		Transport				Import				Export
Country	CODE	Lost	Delay	Broken	Total	Lost	Delay	Broken	Total	Lost	Delay	Broken	Total
Spain	15	1	0	0	1	0	2	1	3	0	0	1	1
France	1	0	1	0	1	0	1	0	1	1	1	1	3
UK	2	0	0	2	2	1	0	0	1	0	1	0	1

Into something like this.:

Country	Code	Type	Issue	Ammount
Spain	15	Transport	Lost	1
Spain	15	Transport	Delay	0
Spain	15	Transport	Broken	0
Spain	15	Import	Lost	0
Spain	15	Import	Delay	2
Spain	15	Import	Broken	1
Spain	15	Export	Lost	0
Spain	15	Export	Delay	0
Spain	15	Export	Broken	1
France	1	Transport	Lost	0
France	1	Transport	Delay	1
France	1	Transport	Broken	0
France	1	Import	Lost	0
France	1	Import	Delay	1
France	1	Import	Broken	0
France	1	Export	Lost	1
France	1	Export	Delay	1
France	1	Export	Broken	1
UK	2	Transport	Lost	0
UK	2	Transport	Delay	0
UK	2	Transport	Broken	2
UK	2	Import	Lost	1
UK	2	Import	Delay	0
UK	2	Import	Broken	0
UK	2	Export	Lost	0
UK	2	Export	Delay	1
UK	2	Export	Broken	0

I have tried with QlikView transform but unable to achieve it.

Any kind soul that could give some insight?

Thanks in advance

Re: Transforming table before loading

fosterma — Mon, 05 Mar 2018 15:06:10 GMT

I think you're looking for the Crosstable() Function.

Load as 3 Tables for simplicity and concatenate.

Solution Below, just comment out 'Total', you'll probably want this as a measure.

Table1:
LOAD
    Country,
    CODE,
    'Transport' AS Type,
    Lost,
    Delay,
    Broken,
    "Total"
FROM [lib://Sample/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(Table1)
LOAD
    Country,
    CODE,
    'Import' AS Type,
    Lost1 AS Lost,
    Delay1 AS Delay,
    Broken1 AS Broken,
    Total1 AS Total
FROM [lib://Sample/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(Table1)
LOAD
    Country,
    CODE,
    'Export' AS Type,
    Lost2 AS Lost,
    Delay2 AS Delay,
    Broken2 AS Broken,
    Total2 AS Total
FROM [lib://Sample/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Final:
CrossTable(Issue,Amount,3)
LOAD * Resident Table1;

Drop Tables Table1;

Re: Transforming table before loading

niclaz79 — Mon, 05 Mar 2018 15:11:13 GMT

Hi,

It's not clear how you differentiate between Transport and Import, but this should get you parts of the way:

Crosstable(Country, CODE, Issue,Amount,2)

LOAD

Country,

CODE,

Lost,

Delay.

Broken

Resident TableName;

Just add Type and increase 2 to 3 and you should be good

Re: Transforming table before loading

isingh30 — Mon, 05 Mar 2018 15:13:56 GMT

Check this -

The Crosstable Load

Thank you!