Dynamic validation of 4 columns

Question

Hi,

What's now:
I have formula that validate data from 4 columns. If string in all 4 columns match, it marks it as a true otherwise as a false. That 4 columns comes from 4 different files.

What I want:

Since not everytime all 4 files will be loaded (instead i load an empty template file with just column names and no data) I need to modify that so it will be processing only those columns which were loaded. And i don't want to create a superlong formula were i manually code all cases.

Do you have any ideas that could help?

Thanks in advance

atcodedog05 · Answer

Hi @I2ufu5

I have modified the workflow to include the scenario.

Workflow:

1. I have added a data cleanse before transpose tool to remove null columns

2. Changed summarize tool to count distinct including null. Now it been also considered as variation.

Hope this helps 🙂

compare.yxmd

I2ufu5 · Answer

That look nice but i need a distinction between null value and column(file) not loaded at all.
What i mean by that is when I load files number 1,2,3 and not 4, workflow will produce that output:

RecordFile1File2File3File4Verification1AAAnullTrue2AnullAnullFalse

First row will be true because File4 wasn't loaded (no data in whole column)
Second row will be false because File2 was loaded but it had a null value at that index.

atcodedog05 · Answer

Hi @I2ufu5

Here is how you can achieve it.

Input: 4 columns with null where data is not loaded

Workflow:

1. Generate record ID as a key

2. Use transpose tool to covert 4 column to rows and set all other columns as keys

3. Use summarize tool, config countdistinctnonnull of values (which will check only non null values). If the count distinct is 1 then all values are same/match.

4. Use formula tool to calculate flag. True if countdistinct is 1 and false if not.

5. Using join tool to map all other columns back.

Hope this helps 🙂

compare.yxmd