Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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
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 !
Hi Priyanka,
After transposing data as single field,group data to different fields like Varience, Score and Cal fields.
Regards
Neetha
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
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
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 !