topic Re: CrossTable data with more than one header in QlikView

CrossTable data with more than one header

Tue, 12 May 2015 10:46:32 GMT

I have data in my below Exel file which I would like to cross table.

Q1, V1 resents data for January [Quantity] and [Value]

Q2, V2 represents data for February [Quantity] and [Value] ...etc

How can I structure this data to be in a logical table:

SBUDA, SBUDD, Month, Quantity, Value?

Re: CrossTable data with more than one header

john_s_w_wood — Tue, 12 May 2015 11:24:05 GMT

might have a solution for you Tumelo. Here's the script, attached sample qvw

tempData:

CrossTable(Field, Value, 2) // first 2 fields are in rows, label rest of data as field and value

LOAD * // wildcard used to replace fields to save space but also accomodates new fields

FROM

TBUDEXPY.xlsx

(ooxml, embedded labels, table is TBUDEXPY);

NoConcatenate

tempData2: // only pull data where field starts with a Q, take right characters after Q as Month

load SBUDA,SBUDD, Right(Field,len(Field)-1) as Month, Value as Quantity resident tempData where left(Field,1) = 'Q';

Concatenate

//concatenate, only pull data where field starts with a V, take right characters after V as Month

load SBUDA,SBUDD,Right(Field,len(Field)-1) as Month, Value resident tempData where left(Field,1) = 'V';

Drop Table tempData;// no longer required

noconcatenate

Data: // consolidate into single row combinations

Load SBUDA,SBUDD,Month, sum(Quantity) as Quantity, sum(Value) as Value resident tempData2

group by SBUDA,SBUDD, Month;

Drop Table tempData2;// no longer required

Re: CrossTable data with more than one header

sunny_talwar — Tue, 12 May 2015 12:21:51 GMT

You can also try this:

Table:

CrossTable(Quantity|Value, Data, 2)

LOAD SBUDA,

SBUDD,

Q1&'|'&V1,

Q2&'|'&V2,

Q3&'|'&V3,

Q4&'|'&V4,

Q5&'|'&V5,

Q6&'|'&V6,

Q7&'|'&V7,

Q8&'|'&V8,

Q9&'|'&V9,

Q10&'|'&V10,

Q11&'|'&V11,

Q12&'|'&V12

FROM

Community_163620.xlsx

(ooxml, embedded labels, table is TBUDEXPY);

Table1:

LOAD SBUDA,

SBUDD,

PurgeChar(PurgeChar(Quantity|Value, '&|'), Chr(39)) as DataType,

SubField(Data, '|', 1) as Quantity,

SubField(Data, '|', 2) as Value

Resident Table;

DROP Table Table;

Best,

Sunny

Re: CrossTable data with more than one header

Tue, 12 May 2015 12:29:58 GMT

Wow! Thanks a lot guys, both your solutions are awesome.

John: Is there a way I can convert Month from 1-12 to Jan-Dec in your solution?

Re: CrossTable data with more than one header

john_s_w_wood — Tue, 12 May 2015 12:34:54 GMT

way i do it is pick(monthNo,'Jan','Feb',etc) so if monthno is say 6 it will return Jun. There might be a slicker way using an inline table with 2 columns monthno and month for a lookup or mapping load or playing with month(makedate(2000,monthno,1)).

Re: CrossTable data with more than one header

sunny_talwar — Tue, 12 May 2015 12:37:41 GMT

You can do this to convert numbers to Month Names:

Dual(Pick(Month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), Month) as Month

Re: CrossTable data with more than one header

Wed, 13 May 2015 06:42:14 GMT

Thanks a lot guys!