To create pivot structure with conditional measures

[Saurabh07]

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!!

[Kushal_Chawda]

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

[Saurabh07]

@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!

[Kushal_Chawda · Accepted solution]

@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)

[Saurabh07]

@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!

[Kushal_Chawda]

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

[Saurabh07]

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