topic Problems "crosstabling" a table in QlikView

Problems "crosstabling" a table

marcel_olmo — Fri, 18 Nov 2011 12:05:55 GMT

Hi guys, here's my problem.

I have a table like this :

And this is my desired result :

I'm trying to solve it with crosstable function, but it's not working, I don't know why.

Anybody knows how to do that??

Many thanks in advance!!!!

Problems "crosstabling" a table

martinpohl — Fri, 18 Nov 2011 12:19:36 GMT

Something like that?

Go to Properties/Presentation and mark Horizontal

Problems "crosstabling" a table

marcel_olmo — Fri, 18 Nov 2011 12:26:58 GMT

Hi Martin, this is not as easy as you think.

I mean the first picture is a table, and the second one is my Desired Table. I want all the rows converted into columns. And it's supposed to be the result of the crosstable function.

Do yo know how to do it?

Re: Problems "crosstabling" a table

Sokkorn — Fri, 18 Nov 2011 12:31:12 GMT

Hi Marcel,

Try this script

[DS]:
LOAD * INLINE [
Field1, Attribute, Value
A, xx, 1
A, yy, 2
A, zz, 3
A, xx, 4
A, yy, 5
];
[TMP1]:
GENERIC LOAD * RESIDENT [DS];
[RESULT]:
LOAD DISTINCT Field1 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;

HTH and let me know.

Regards,

Sokkorn

Problems "crosstabling" a table

marcel_olmo — Fri, 18 Nov 2011 13:12:11 GMT

Hi Cheav, this is what I was looking for!!

I didn't know it was gonna be so hard. Now it works like a charm.

Many thanks!!

Regards, Marcel.