Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sayadutt
Creator
Creator

Categorize a field with values and creating a pivot chart

Hi All,

I have data like below

MonthA: PassA: FailB:PassB:Fail
Jan10102030
Feb20202020

 

What is the best way to categorize the above data and create a pivot table out of it. I am looking for the below pivot table

Need Group A and B at top (horizontally)

GroupA B 
MonthA: PassA: FailB:PassB:Fail
Jan10102030
Feb20202020

 

1 Solution

Accepted Solutions
Rodj
Luminary Alumni
Luminary Alumni

Basically you need to pivot your data and create another field with the values 'A' and 'B' that you can use as a dimension in your pivot table. First we pivot the table using the crosstable function:

Temp_table:

Crosstable(Category, Value, 1)

Load

  Month,

  A: Pass,

  A: Fail,

  B:Pass,

  B:Fail

<from whatever your source is>

 

Then we can derive the new column:

Final_table:

Load

  Month,

  Category,

  if(FindOneOf(Category, 'A:') > 0, 'A',

    if(FindOneOf(Category, 'B:') > 0, 'B')) as Group,

  Value;

resident Temp_table;

drop table Temp_table; //as it is no longer needed

 

Now you can build your pivot table with Month, Group and Category as dimensions and value as your measure. Aligning them the way you want is just a matter of moving them around.

I hope that puts you on the right track.

View solution in original post

3 Replies
Rodj
Luminary Alumni
Luminary Alumni

Basically you need to pivot your data and create another field with the values 'A' and 'B' that you can use as a dimension in your pivot table. First we pivot the table using the crosstable function:

Temp_table:

Crosstable(Category, Value, 1)

Load

  Month,

  A: Pass,

  A: Fail,

  B:Pass,

  B:Fail

<from whatever your source is>

 

Then we can derive the new column:

Final_table:

Load

  Month,

  Category,

  if(FindOneOf(Category, 'A:') > 0, 'A',

    if(FindOneOf(Category, 'B:') > 0, 'B')) as Group,

  Value;

resident Temp_table;

drop table Temp_table; //as it is no longer needed

 

Now you can build your pivot table with Month, Group and Category as dimensions and value as your measure. Aligning them the way you want is just a matter of moving them around.

I hope that puts you on the right track.

Brett_Bleess
Former Employee
Former Employee

Did Rodj's post get you what you needed to get your layout as you wished?  If so, do not forget to return to this thread and use the Accept as Solution button on that post to give them credit for the assistance.  This also confirms to other Community Members that things worked too.  If you are still working upon things, please leave an update.  Since you do have the other post as well, you may also want to provide the link to each of them in the other post too.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Rodj
Luminary Alumni
Luminary Alumni

Apparently you came up with the solution Brett, well done! 😉

@sayadutt  I don't know if you can fix it, but it would be nice if you did.