topic Re: Categorize a field with values and creating a pivot chart in QlikView

Categorize a field with values and creating a pivot chart

sayadutt — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data like below

Month	A: Pass	A: Fail	B:Pass	B:Fail
Jan	10	10	20	30
Feb	20	20	20	20

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)

Group	A		B
Month	A: Pass	A: Fail	B:Pass	B:Fail
Jan	10	10	20	30
Feb	20	20	20	20

Re: Categorize a field with values and creating a pivot chart

Rodj — Tue, 07 Jan 2020 21:03:24 GMT

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

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.

Re: Categorize a field with values and creating a pivot chart

Brett_Bleess — Wed, 05 Feb 2020 20:47:33 GMT

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

Re: Categorize a field with values and creating a pivot chart

Rodj — Thu, 06 Feb 2020 07:24:44 GMT

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.