topic Cross Tables in QlikView

Cross Tables

marco_puccetti — Mon, 09 Sep 2013 08:02:25 GMT

Hello i need to create a Table structured like this:

Year	Metal	Kg
2000	Copper	17
2000	Silver	14
2000	Gold	25
2001	Copper	20
2001	Silver	21
2001	Gold	22

From a csv with this data inside:

Year	Copper	Silver	Gold
2000	17	14	25
2001	20	21	22

Anybody Knows how it can be made using cross tables?

Thank you

Marco

Re: Cross Tables

johanlindell — Mon, 09 Sep 2013 08:10:56 GMT

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.

Re: Cross Tables

orital81 — Mon, 09 Sep 2013 08:14:36 GMT

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:

Re: Cross Tables

johanlindell — Mon, 09 Sep 2013 08:16:29 GMT

That is a better crosstable, Ori.

Johan

Re: Cross Tables

orital81 — Mon, 09 Sep 2013 08:22:53 GMT

Thanks

Re: Cross Tables

Mon, 09 Sep 2013 08:35:05 GMT

PFA

Re: Cross Tables

marco_puccetti — Mon, 09 Sep 2013 08:54:43 GMT

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

Thank you

Marco

Re: Cross Tables

johanlindell — Mon, 09 Sep 2013 08:59:06 GMT

Look at Ori's example.

Re: Cross Tables

marco_puccetti — Mon, 09 Sep 2013 09:47:55 GMT

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

Re: Cross Tables

Sokkorn — Mon, 09 Sep 2013 09:53:56 GMT

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

Re: Cross Tables

orital81 — Mon, 09 Sep 2013 11:17:02 GMT

Hi Marco

The result of crosstable is that the column headers (Copper,Silver,Gold) will be stored in one field (Metal) the attribute field, the column data will be stored in a second field (KG).

For more info go to Qlikview Help index.

Hope this was useful for you.

Ori