Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Transforming table before loading

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:

 

TransportImportExport
CountryCODELostDelayBrokenTotalLostDelayBrokenTotalLostDelayBrokenTotal
Spain15100102130011
France1010101011113
UK2002210010101

Into something like this.:

 

CountryCodeTypeIssueAmmount
Spain15TransportLost1
Spain15TransportDelay0
Spain15TransportBroken0
Spain15ImportLost0
Spain15ImportDelay2
Spain15ImportBroken1
Spain15ExportLost0
Spain15ExportDelay0
Spain15ExportBroken1
France1TransportLost0
France1TransportDelay1
France1TransportBroken0
France1ImportLost0
France1ImportDelay1
France1ImportBroken0
France1ExportLost1
France1ExportDelay1
France1ExportBroken1
UK2TransportLost0
UK2TransportDelay0
UK2TransportBroken2
UK2ImportLost1
UK2ImportDelay0
UK2ImportBroken0
UK2ExportLost0
UK2ExportDelay1
UK2ExportBroken0

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

Any kind soul that could give some insight?

Thanks in advance

1 Solution

Accepted Solutions
fosterma
Contributor III
Contributor III

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;

View solution in original post

3 Replies
fosterma
Contributor III
Contributor III

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;

niclaz79
Partner - Creator III
Partner - Creator III

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

isingh30
Specialist
Specialist

Check this -

The Crosstable Load

Thank you!