Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marco_puccetti
Partner - Creator
Partner - Creator

Cross Tables

Hello i need to create a Table structured like this:

YearMetalKg
2000Copper17
2000Silver14
2000Gold25
2001Copper20
2001Silver21
2001Gold22

From a csv with this data inside:

YearCopperSilverGold
2000171425
2001202122

Anybody Knows how it can be made using cross tables?

Thank you

Marco

10 Replies
johanlindell
Partner - Creator II
Partner - Creator II

One way is to solve it in the script. You can do this by loading the file three times, one for each  metal.

See attached example.

orital81
Partner - Creator III
Partner - Creator III

You can do it in 1 load

CrossTable(METAL,KG)

LOAD Year,

     Copper,

     Silver,

     Gold

FROM

Test1.xls

(biff, embedded labels, table is [Sheet1$]);

See Attached:

johanlindell
Partner - Creator II
Partner - Creator II

That is a better crosstable, Ori.

Johan

orital81
Partner - Creator III
Partner - Creator III

Thanks

Not applicable

PFA

marco_puccetti
Partner - Creator
Partner - Creator
Author

Is there any other way to get the same result without using single load statements?

Thank you

Marco

johanlindell
Partner - Creator II
Partner - Creator II

Look at Ori's example.

marco_puccetti
Partner - Creator
Partner - Creator
Author

I've seen the post although i've not understood well the CrossTable command's logic:

CrossTable(METAL,KG)

LOAD Year,

     Copper,

     Silver,

     Gold

FROM

Test1.xls

(biff, embedded labels, table is [Sheet1$]);

In particular can you explain how the bold command works to generate the final data?

Thank you

Marco

Sokkorn
Master
Master

Hi Marco,

Let try script below:

[DS]:

LOAD * Inline [

Year    ,    Metal    ,    Kg

2000    ,    Copper    ,    17

2000    ,    Silver    ,    14

2000    ,    Gold    ,    25

2001    ,    Copper    ,    20

2001    ,    Silver    ,    21

2001    ,    Gold    ,    22];

[TMP1]:

GENERIC LOAD * RESIDENT [DS];

[RESULT]:

LOAD DISTINCT Year RESIDENT [DS];

DROP TABLE [DS];

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) AS Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'TMP1.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

drop table TableList;

See sample attached file also.

Regards,

Sokkorn