transpose table

[#1]

Hey all,

I load tables with following structure:

ID	2014	2015	2016
1	15	2	6
2	12	52	5
n	...	...	...

Every table represents one list. So I have for example 2 lists with the shown structure. While loading I use "Join load" so I end up with one QlikView table containing all information. Result is 4 fields, ID, 2014, 2015 and 2016. Now I would like to realize a diagramm with two dimensions. One dimension (first one) should give information about the values related to the year. The second dimension contains information about the list. Means the diagramm should look like this:

IMHO the diagramm can't be realized with the existing table strucutre. so first question: Is it possible and I just messed it up?

However I assumed I need an other table structure. So I would transpose the existing table to end up with this structure:

ID	Year	Value	List
1	2014	15	list_1
1	2015	2	list_1
1	2016	6	list_1
1	2014	from list 2	list_2
1	2015	from list 2	list_2
1	2016	from list 2	list_2
2	2014	12	list_1
2	2015	52	list_1
2	2016	5	list_1
2	2014	from list 2	list_2
2	2015	from list 2	list_2
n	...	...	...

And here comes the second question: How do I correctly transpose the existing table structure to end up with my suggestion? AND ist it even the correct way to realize this bar chart?

Thanks a lot to every single reply!

[Ralf-Narfeldt]

If you do Crosstable loads of the two tables, they should look like what you want to achieve.

[jagan · Accepted solution]

Hi,

Try to use CrossTable() like below

List:

LOAD

'List1' AS DataType,

*

FROM List1;

Concatenate(List)

LOAD

'List2' AS DataType,

*

FROM List2;

CrossTable(Year, Value, 3)

LOAD

*

FROM List;

DROP TABLE List;

Hope this helps you.

Regards,

Jagan.

[#4]

Hey Jagan,

thanks a lot. Thats exactly what I was looking for!!! Just that I need to use "Resident" instead of "From", but everything else worked out.