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
Solved! Go to Solution.
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
Thanks Ben, It worked like a charm :)
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
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.