Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

marco_puccetti
Contributor

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

Re: Cross Tables

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
Contributor III

Re: Cross Tables

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

Re: Cross Tables

That is a better crosstable, Ori.

Johan

orital81
Contributor III

Re: Cross Tables

Thanks

Not applicable

Re: Cross Tables

PFA

marco_puccetti
Contributor

Re: Cross Tables

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

Thank you

Marco

johanlindell
Contributor II

Re: Cross Tables

Look at Ori's example.

marco_puccetti
Contributor

Re: Cross Tables

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
Honored Contributor

Re: Cross Tables

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

Community Browser