Summarize based on conditional statements

[akanksha01]

I have a data set like this, 

Parent	Region	Fig.1	Fig.2	Type	Class
A	Germany	456	67	Y	R
A	Ireland	0	98	N	I
B	Germany	87	24	N	I
B	Ireland	98	0	Y	R
B	UK	45	0	Y	I
C	US	57	6	Y	R
C	Lux	0	90	N	R

Want to create different output based on following conditions. Please note that I cannot use formula tool to define specific region (Germany) or Parent names (A,B,C) as the source data sheet has many of them. These conditions are more or less similar hence even if I get solution for 1 or 2 that will be really helpful.

Condition 1: If Fig.1 for Parent in a region is "0" OR the region is not present in dataset for the Parent then "No Data", else if Fig.1 of Class "R" for a Parent in a region is >= 25% of sum of Fig.1 for that Parent in all regions then "Oppt'y" else "No Oppt'y" EndIf

Expected Output

Parent	Germany	UK	Ireland	Lux	US
A	Oppt'y	No Data	No Data	No Data	No Data
B	No Oppt'y	No Oppt'y	Oppt'y	No Data	No Data
C	No Data	No Data	No Data	No Data	Oppt'y

Condition 2: Condition for "No Data" remains same, else if Fig.2 of Type "Y" for a Parent in a region is >= 10% of sum of Fig.1 for that Parent in all regions then "Oppt'y" else "No Oppt'y" EndIf

Expected Output

Parent	Germany	UK	Ireland	Lux	US
A	Oppt'y	No Data	No Data	No Data	No Data
B	No Oppt'y	No Oppt'y	No Oppt'y	No Data	No Data
C	No Data	No Data	No Data	No Data	Oppt'y

Condition 3: Condition for "No Data" remains same, else if Fig.2 for a Parent in a region is Greater than 0 but less than 70 then "Oppt'y" else "No Oppt'y" EndIf

Expected Output

Parent	Germany	UK	Ireland	Lux	US
A	Oppt'y	No Data	No Data	No Data	No Data
B	Oppt'y	No Oppt'y	No Oppt'y	No Data	No Data
C	No Data	No Data	No Data	No Data	Oppt'y

Condition 4: Condition for "No Data" remains same, else if Fig.2 for a Parent in a region is "0" then "Oppt'y" else "No Oppt'y" EndIf

Expected Output

Parent	Germany	UK	Ireland	Lux	US
A	No Oppt'y	No Data	No Data	No Data	No Data
B	No Oppt'y	Oppt'y	Oppt'y	No Data	No Data
C	No Data	No Data	No Data	No Data	No Oppt'y

Please suggest. 

Thanks in advance

[BenMoss]

Hey,

I like the challenge.

I'll try get something over to you soon but it's clearly going to go beyond just a couple of tools I think.

Ben

[BenMoss · Accepted solution]

Hey akanksha01,

Here is the solution I came up with.

Please walk through it and if you have any questions please reach out!

Ben

[akanksha01]

Thanks Ben, It worked like a charm :)

[SeanAdams · Accepted solution]

Hey @akanksha01

Also took a bash like @BenMoss - slightly different approach.

Some things to notice:

- I had 3 inputs - one is your input data; second is a clean list of regions; third is a list of parents.   Reason for doing this is that you may not get a particular parent on any given day, and if you only rely on your input data to spot this you'd miss the fact that this should be reported as "no data"

- In there's an append that joins the two clean lists together.   Technically this is called a Cartesian product - it blows out every combination of region and parent.   This is critical to spot missing combinations

- I didn't redo the "no-data" pieces in each condition - just joined them into the final union for each condition - the connections are marked as "wireless" so you can't see them

- Finally - on each of the conditions - the data arrives as a 3 column table - Parent; region and OpportunityTag.   A quick Cross-tab changes this into the final format you needed.

Hopefully this answers your question (along with @BenMoss 's solution you now have two ways to tackle) - if so, could you mark this as solved?   If you have additional questions, feel free to post them here

have a good weekend

Sean

[akanksha01]

Thanks Sean for this.

You are right, there are cases when a parent region combination was not present in data-set. which may result in No Data. I applied your method as well and only at the cross tab level I had to change method as "First" instead of Concatenate and it resulted in the desired output.

Thanks again.