Summarize based on conditional statements

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

Common Use Cases

Accepted answers

BenMoss

Hey akanksha01,

Here is the solution I came up with.

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

Ben

Conditional Oppertunity.yxzp

SeanAdams

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

SummarizeBasedOnCondition.yxmd

All comments

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

Hey akanksha01,

Here is the solution I came up with.

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

Ben

Conditional Oppertunity.yxzp

akanksha01

Thanks Ben, It worked like a charm

SeanAdams

Hey @akanksha01

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

Some things to notice:

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

have a good weekend

Sean

SummarizeBasedOnCondition.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703