Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Saro_2306
Contributor II
Contributor II

Cross Table

Hi Friends,

I have Data Column name with Month and Category ( i.e Jan 2021 - Attrition). 3 columns for each Month. While loading excel with cross table i can able to direct column name only. But my need is Month should be in separate column , Category should be in 2nd column and data should be in 3rd Column. Please refer attached image for easy understand. 

Is there any way to do like this. Any body can help. Thanks in Advance.

Jan - 2019 - AnnualizedJan - 2019 - Total Attrition Jan - 2019 - HeadcountFeb - 2019 - AnnualizedFeb - 2019 - Total Attrition Feb - 2019 - Headcount

 

1 Reply
rubenmarin

Hi, I didn't tested but maybe you can do this on 2 steps

- First step: standar Crosstabla, creating a "ColumnHeader" field

- Second step creating the fields you want from this one:

  Date#(Left([ColumnHeader], Index([ColumnHeader],'2021')+3),'MMM - YYYY) as Month,
  Mid([ColumnHeader],  Index([ColumnHeader],'2021')+4) as Category

The '2021' string would be better if it's changed by something more dynamic, like a variable that stores Year(Today()) or something like that, it depends on the data you want to load.