Hi,
I need to run a very basic If Then statement on multiple fields in my data. At first I was thinking I could use the Multi-Formula tool, but since it has multiple field inputs not sure that it would work.
Essentially, I have a dataset where I unioned two datasets of the same fields but with different as of dates and I need to compare each of those fields to each other. For Ex my dataset looks like:
Day 0_Code | Day 1_Code | Day 0_Rate | Day 1_Rate |
A | A | 1 | 1.5 |
B | C | 2 | 2 |
And I need to run this formula: IF Day 0_"Field" = Day 1_"Field" THEN 'Y' ELSE 'N" ENDIF that will turn it into this:
Day 0_Code | Day 1_Code | Match? | Day 0_Rate | Day 1_Rate | Match? |
A | A | Y | 1 | 1.5 | N |
B | C | N | 2 | 2 | Y |
My problem is that I have around 400 fields total that this needs to be ran on so doing it manually would take a long time.
I don't think the Multi-formula tool will work since from what I understand it only runs on one field at a time rather than two. Would a macro possibly work for this?
Thank you!
Solved! Go to Solution.
Thanks for the response! How does that work if i need a new field created for every "Match?" scenario?
Here's a way to do it for your data. It takes a couple of rounds of transpose/crosstab. You might be able to simplify that by combining the two datasets differently earlier in your process.
I adjusted your column names so they would sort correctly - cross-tabbed fields sort alphabetically.
Hi @kfish
Checked with more input fields, the workflow will work dynamically.
For your information as @Christina_H and my solution does not have the column name as Match? because we cant have more than 1 column with the same column name.
Hence something to be added before/after the column name to make the column name unique.
Please let me know if you have any more questions.
Many thanks
Shanker V
Thanks!! This is so close to working, but for some reason when it transposes back to the original format, I lose match data on all the same rows. Do you know why that would happen?
Sorry for my horrible shading, just is data that i can't share!
Hi @kfish
Could you please share more information to analyze please.
Can you compare on my workflow and your applied workflow and let me know in which step you see a difference.
Many thanks
Shanker V
Hi @ShankerV I ended up figuring it out! Im running this on around 400 fields and one of the Day 1 fields was missing their Day 0 fields so pulled it in and it fixed the issue. Thank you!