topic Re: To create pivot structure with conditional measures in App Development

To create pivot structure with conditional measures

Saurabh07 — Mon, 20 Dec 2021 21:08:46 GMT

Hi,

I have a set of mentioned data;

Region	Country	Class	Segment	Class 1 Value	Class 3 Value	Class 2 Value	Class 4 Value
Asia	India	Class 1	Seg A	10			50
Asia	India	Class 1	Seg B	30		90
Asia	India	Class 1	Seg C		40
Asia	Japan	Class 2	Seg A		15	20
Asia	Japan	Class 2	Seg B	10
Asia	Japan	Class 2	Seg C		60	60	15
Asia	Australia	Class 3	Seg A		70
Asia	Australia	Class 3	Seg B	30	40	90	80
Asia	Australia	Class 3	Seg C	50	40
Europe	Germany	Class 4	Seg A	20			10
Europe	Germany	Class 4	Seg B		70	60
Europe	Germany	Class 4	Seg C	90	80	40	20

My requirement here is to have a pivoted structure with the segments at column level. But the real challenge is to get the sum of values based on the conditions. Basically, if a country belongs to Class 1, I want the values to be picked from 'Class 1 Value' field, if a country belongs to Class 2, the values should be picked from 'Class 2 Value' field and so on. Similarly the values for each segments should be picked from respective Class value columns.

Below is the desired table I want in Qlik Sense for better clarity of my requirement;

Region	Country	Class	Total Value	Seg A	Seg B	Seg C
Asia	India	Class 1	40	10	30	0
Asia	Japan	Class 2	80	20	0	60
Asia	Australia	Class 3	150	70	40	40
Europe	Germany	Class 4	30	10	0	20

I tried a lot, but with no luck. Any help here would be highly appreciated!!

Re: To create pivot structure with conditional measures

Kushal_Chawda — Mon, 12 Oct 2020 10:49:33 GMT

@Saurabh07 do you want it in script or frond end?

Re: To create pivot structure with conditional measures

Saurabh07 — Mon, 12 Oct 2020 11:01:23 GMT

@Kushal_Chawda The desired table is required in the front end in form of a Pivot or data Table. The challenge I am facing is to model the data to get this view, which I believe would be done in script editor! Correct me here if I am wrong!

Re: To create pivot structure with conditional measures

Kushal_Chawda — Mon, 12 Oct 2020 11:21:15 GMT

@Saurabh07 try below

Data: CrossTable(ClassName,ClassValue,4) LOAD Region, Country, "Class", Segment, "Class 1 Value", "Class 3 Value", "Class 2 Value", "Class 4 Value" FROM [lib://Web] (html, utf8, embedded labels, table is @1); Final: NoConcatenate Load *, if(WildMatch(trim(Lower(ClassName)),'*'& lower(trim("Class"))&'*'),1,0) as Class_Match Resident Data; Drop Table Data;

then you can create the pivot table with below expression

Sum({<Class_Match={1}>}ClassValue)

Re: To create pivot structure with conditional measures

Saurabh07 — Tue, 13 Oct 2020 09:20:21 GMT

@Kushal_Chawda Thanks man for the solution, it worked! But there is only one small addition which I mentioned earlier. I also need the aggregated value for each country as 'Total Value'. How do I get that ? Do you suggest if I aggregate it in the backend and convert it to text, hence I can add it as a dimension in my Pivot table? This way Ill have only one measure splitted on the Segment and the Total Value will remain unaffected!

Re: To create pivot structure with conditional measures

Kushal_Chawda — Tue, 13 Oct 2020 10:19:05 GMT

@Saurabh07 for total you can simply enable total option for the dimension segment.

Re: To create pivot structure with conditional measures

Saurabh07 — Tue, 13 Oct 2020 10:48:33 GMT

@Kushal_Chawda Yup it worked! Thank you for this. It helped a lot, this was most important part of my report! Appreciate it!