topic Re: CrossTable in QlikView

CrossTable

Tue, 17 Feb 2015 10:11:34 GMT

Hi,

I have a spreadsheet in the below format;

There are sets of 'Particular' like 'Brand','Distribution'. etc such 5 and 'Variance','Score','Cal' against each of the 'Particular' repeating for 12 Months.

The issue is with the Format it is placed.

CrossTable might be the solution, but unable to implement in a right manner.

Any help appreciated!

Thanks !

Re: CrossTable

Anonymous — Tue, 17 Feb 2015 10:16:39 GMT

Hi,

See Multi header pivot import http://community.qlik.com/docs/DOC-4527

Regards

Neetha

Re: CrossTable

tobias_klett — Tue, 17 Feb 2015 10:27:39 GMT

Hi Priyanka,

what you are looking for is not Crosstable but Transponse.

Transponse:

LOAD @1 as Month,

@2 as KPI,

@3 as Value

FROM

CrossTable_orNot.xlsx

(ooxml, no labels, table is Crosstable, filters(

Transpose()

));

Hope this helps
Regards Tobias

Re: CrossTable

Tue, 17 Feb 2015 11:43:37 GMT

Hi,

Appreciate the response.

But, If you observe, the Varience, Score and Cal fields, in your mentioned case 'KPI' are to be taken as 3 Different Fields, Also if you notice they are repeating for every Month against it.

Can you please share any idea to deal with it?

Thanks !

Re: CrossTable

Anonymous — Tue, 17 Feb 2015 11:48:59 GMT

Hi Priyanka,

After transposing data as single field,group data to different fields like Varience, Score and Cal fields.

Regards

Neetha

Re: CrossTable

tobias_klett — Tue, 17 Feb 2015 12:00:49 GMT

Hi Priyanka,

change the script to:

Transponse:

LOAD @1 as Month,

// @2 as KPI,

@3 as Variance

FROM

CrossTable_orNot.xlsx

(ooxml, no labels, table is Crosstable, filters(

Transpose()

))

where @2 = 'Variance';

left join (Transponse)

LOAD @1 as Month,

// @2 as KPI,

@3 as Score

FROM

CrossTable_orNot.xlsx

(ooxml, no labels, table is Crosstable, filters(

Transpose()

))

where @2 = 'Score';

left join (Transponse)

LOAD @1 as Month,

// @2 as KPI,

@3 as Cal

FROM

CrossTable_orNot.xlsx

(ooxml, no labels, table is Crosstable, filters(

Transpose()

))

where @2 = 'Cal';

Regards Tobias

Re: CrossTable

Anonymous — Tue, 17 Feb 2015 12:03:07 GMT

Hi,

Maybe try in script:

if(Data = 'Varience',Values) as Varience,
if(Data = 'Score',Values) as Score,
if(Data= 'Cal',Values) as Cal;

Regards

Neetha

Re: CrossTable

Tue, 17 Feb 2015 12:24:15 GMT

Hi,

Thanks a lot for the reply, they have been very helpful !

Another thing, If you notice in the Screen shot, there are multiple rows for the Values which contradicts the assumption '@3' being the only row containg value for each KPI, for each Month ..

Also, there is a 'Particulars' Field which is the Main Metrics Field for each of the value present.

Would like more of your insight on it !

Thanks in advance !