Cross tab within cross tab

[manish_amin]

Hello,

I come from Tableau background and don't know much about qliksense. I have below requirement. I would appreciat if I can get help.

As attached in sheet, columns F through L contains different trigger name,trigger values and an event names.

I need to have following columns so that i can compare trigger1 value with trigger1 threshold and can create another field which checks for the breach(trigger1 value<trigger1 threshold).

Also, there are events which has values like YES/NO.

So i want 2 columns seperately as mentioned below.

Trigger name with values

Trigger threshold with threshold values

I tried crosstab but don't know how to nest it.

[deepanshuSh]

If I am interpetating your data correctly, you are basically trying to convert the numeric trigger values and threshold values by comparing them to each other and comparing the event values corresponding to yes and no present under the same column trigger value to create a new column for each. To do this, you can  write an if and else statement separately, as you are new to qlik so it would seem a little less confusing to you to use two different if and else statements, like. 

if (Num(trigger1 value)>num(trigger1 threshold), 'Threshold_exceeded',

if(Num(trigger1 value)<num(trigger1 threshold), 'Below_threshold') )as Threshold_calculation,

if (Trigger1 value ='yes', 'Event_complete,

if (Trigger1 Value  = 'No', event_incomplete)) as Event_calculation

No need to put the else condition as you trying to create two separate columns using same field. 

Hope it Helps!

[manish_amin]

He Deepanshu, appreciate your reply. But if you look at the sheet, I mentioned the requirement as well. I need 3 columns. 1. Trigger name 2. Trigger value 3.Threshold for which i need to transpose all such columns to rows. Bu the challenge is to sepearate the trigger name and trigger level columns from the same column.

[deepanshuSh]

Hi Manish, if you just want to separate out the components based on their values or content, you can use the subfield and substring function. 

[manish_amin · Accepted solution]

Thanks Deepanshu but I fixed it. Created 2 seperate pivot tables using Crosstable and then joined them using

common key

[deepanshuSh]

Perfect 😄