topic Re: Crosstable in QlikView

Crosstable

Tue, 15 Jan 2013 23:17:31 GMT

Hi,

I'm trying to create a table in the format :

Year, Item, Value/Qty and Month

At the moment it has a value for each month going horizontally instead a long skinny format.

It is currently in the format:

Year	Item	SMQ01	SMQ02	SMQ03	SMQ04	SMQ05	SMQ06	SMQ07	SMQ08	SMQ09	SMQ10	SMQ11	SMQ12
2010	a	0	0	0	0	0	1900	780	0	0	0	0	0
2011	a	0	0	0	1830	2045	0	0	1951	2285	1325	1691	1700
2012	a	832	1039	1039	0	0	0	0	0	0	0	0	0

Required format :

Year	Item	Month	Value
2012	a	1	832
2012	a	2	1039
2012	a	3	1039

Any ideas?

Many thanks

Re: Crosstable

renjithpl — Wed, 16 Jan 2013 05:19:35 GMT

Try

Root:
CrossTable(Month, Value, 2)
LOAD Year,
     Item,
     SMQ01,
     SMQ02,
     SMQ03,
     SMQ04,
     SMQ05,
     SMQ06,
     SMQ07,
     SMQ08,
     SMQ09,
     SMQ10,
     SMQ11,
     SMQ12
FROM
crosstable.xlsx
(ooxml, embedded labels, table is Sheet1);

New:
load
*,
Right(Month, 2) as MonthNew
Resident Root;
drop Table Root;

Re: Crosstable

Wed, 16 Jan 2013 06:12:23 GMT

try this,

CrossTable(month, value, 2)

LOAD year,

item,

SMQ01,

SMQ02,

SMQ03,

SMQ04,

SMQ05,

SMQ06,

SMQ07

FROM

C:\crosstable.xlsx

(ooxml, embedded labels, table is Sheet1);

load

value,

Right(month, 2) as month2

Resident a;

drop Table a;

hope this helps you.

Re: Crosstable

Wed, 16 Jan 2013 11:08:46 GMT

please see the attached file.this file show the output like this

Required format :

Year	Item	Month	Value
2012	a	1	832
2012	a	2	1039
2012	a	3	1039