Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Crosstable

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:

YearItemSMQ01SMQ02SMQ03SMQ04SMQ05SMQ06SMQ07SMQ08SMQ09SMQ10SMQ11SMQ12
2010a00000190078000000
2011a000183020450019512285132516911700
2012a83210391039000000000

Required format :

YearItemMonthValue
2012a1832
2012a21039
2012a31039

Any ideas?

Many thanks

3 Replies
renjithpl
Valued Contributor

Re: Crosstable

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;

Not applicable

Re: Crosstable

hi

try this,

a:

CrossTable(month, value, 2)

LOAD year,

     item,

     SMQ01,

     SMQ02,

     SMQ03,

     SMQ04,

     SMQ05,

     SMQ06,

     SMQ07

FROM

C:\crosstable.xlsx

(ooxml, embedded labels, table is Sheet1);

b:

load

value,

Right(month, 2) as month2

Resident a;

drop Table a;

hope this helps you.

Not applicable

Re: Crosstable

hi

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

Required format :

YearItemMonthValue
2012a1832
2012a21039
2012a31039
Community Browser