If statement on Set Analysis

[#1]

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.

[#2]

LIke this?

[shawn-qv]

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.

S.

[#4]

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

[#5]

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.

[shawn-qv]

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.

[#7]

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?

[#8]

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

[CELAMBARASAN]

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?

[#10]

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