topic If statement on Set Analysis in QlikView

If statement on Set Analysis

Wed, 04 Jun 2014 22:35:08 GMT

Can somebody help? I have two tables:

Table1:

GroupA, ID

Employees-Male, 1

Employees-Female, 2

Managers, 3

T_Employees, 4

T_Managers, 5

Overall, 6

Table2:

GroupB, ID, Count

Employees-Male, 1, 150

Employees-Female, 2, 200

Managers, 3, 25

What I would like to get an expression on is if I select fields in Table1:

Employees-Male - Count is 150

Employees-Female - Count is 200

Managers - Count is 25

T_Employees - Count is 350 (sum of male & female)

T_Managers - Count is 25 (Managers)

Overall - Count is 375 (all)

I'm thinking if statements in Set Analysis but not sure how to do it.

I already have this expression to begin with which outputs the count as field in Table1 is selected which solves the first three.

sum({<Table2= P(Table1)>} Count)

Since Table2 is just a subset of Table1, the last three (T_Employees, T_Managers, and Overall) is zero. I probably need to add conditions (IF statements) to satisfy conditions.

Any help would be appreciated.

Re: If statement on Set Analysis

Wed, 04 Jun 2014 23:05:24 GMT

LIke this?

Re: If statement on Set Analysis

shawn-qv — Thu, 05 Jun 2014 07:41:51 GMT

There's are few ways you can make this work, but my suggestion is for you to do this in the script as oppose to doing the calculation in the chart. In fact, you should be able to do this without using IF statements.

What Dariusz suggested in his example in one way of doing it, and it uses an inline matrix to manage the GroupA to GroupB relationship.

Providing the ID value doesn't change in you data, another way to do achieve this can done through code. S !ee example.

NOTE: This method performs a full outer join on the 2 tables. For performance reason, this method may not be ideal if you're working with a very large data set.

Re: If statement on Set Analysis

Thu, 05 Jun 2014 17:37:03 GMT

This looks like a good solution but it created a loop since I have 'month' as another column for both tables. Any suggestion?

Re: If statement on Set Analysis

Thu, 05 Jun 2014 17:39:02 GMT

Have not tried this but would this create a loop as Dariusz's approach? I missed to mention that I have 'month' column in both tables.

Re: If statement on Set Analysis

shawn-qv — Thu, 05 Jun 2014 23:23:12 GMT

If you're referring to my example, you can simply include the column "month" into Table1, and also all the concatenating loads in Table2 and you should be fine.

You'll see in my example that you only end up with one table in your data model (and not 2 tables), so you won't get any loops.

Re: If statement on Set Analysis

Fri, 06 Jun 2014 16:16:48 GMT

I tried this but it created synthetic key which I'm trying to avoid. Is there a way to do it in the expression with an if statement?

Re: If statement on Set Analysis

Fri, 06 Jun 2014 16:38:19 GMT

Remove months from table1 and table 2 and put it only into my table3. I understand, that you connect ID1 and ID2 from the same month....

regards

Darek

Re: If statement on Set Analysis

CELAMBARASAN — Fri, 06 Jun 2014 16:41:53 GMT

if you want it to be done in expression then it will be

If(GroupA= 'T_Employees', $(=Sum({<GroupB= {'Employees-Female', 'Employees-Male'}>} Count)), If(GroupA='T_Managers', $(=Sum({<GroupB= {'Managers'}>} Count)), Sum(Count)))

But preferrably you it should be done in script, I'm confused with your statement "Month field is in both tables".

Could you upload same file that your working?

Re: If statement on Set Analysis

Fri, 06 Jun 2014 16:43:39 GMT

Not sure how I can add the month column to table3. It is just a relational table between tables 1 and 2.

Re: If statement on Set Analysis

Fri, 06 Jun 2014 20:03:26 GMT

it worked. just had to delete the $ sign.

If(GroupA= 'T_Employees', Sum({<GroupB= {'Employees-Female', 'Employees-Male'}>} Count)), If(GroupA='T_Managers', Sum({<GroupB= {'Managers'}>} Count)), Sum(Count)))