CrossTable

[#1]

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 !

[Anonymous]

Hi,

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

Regards

Neetha

[tobias_klett]

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

[#4]

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 !

[Anonymous]

Hi Priyanka,

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

Regards

Neetha

[tobias_klett]

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

[Anonymous]

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

[#8]

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 !