Append a column to the output

Hi all,

I have two excel files 'Verification' and 'Verification 2'.

Verification 2 is the actual output file to be achieved. I am trying to prepare data from the Verification file and want to get the results as a Verification 2 file

I have extracted and achieved the same number of records as in Verification 2, but, this output does not have a 'Affiliate Plant Key' column. If I include this column in the summarize tool, I would get different records.

But, the case is that we should have the same number of records and the same type of data as the Verification 2 output. How do I get data like Verification 2 when the affiliate plant key column is added to the Verification output? There should be no difference.

I compared the output I got from Verification with the Verification 2 file and I saw all the records joining.

But, when I add the affiliate plant key column I get the wrong data. How can I get data like Verification 2 for Verification output when using the Affiliate Plant Key Column?

Please check and help.

Thank you.

Datasets

Preparation

Accepted answers

danilang

Hi @stj1120

Since your data has either a dummy [Affiliate Plant Key] per country or a dummy and a single numeric value, you can add a summary tool with this configuration

"#" comes before numbers alphabetically, so this retrieves a single [Affiliate Plant Key] per country. The value is either # or the numeric one if it exists. Join this back to your data and you get your desired output.

Dan

workflow.yxzp

danilang

Hi @stj1120

We're now at the point where you need to determine how the validation2 file was produced. What are the operations that went into making it? There is a step in there, i.e. grouping the data or doing a vlookup that needs to be found. Once you determine this, you can reproduce this in Alteryx.

Dan

All comments

danilang

hi @stj1120

If you look at the Input data in [Affiliate Plant Key] you'll see that that there are dummy values. Filter on [country]="New Zealand" and [Month]="May" and you can see these

Once you deal with those, you'll be able to group by [Affiliate Plant Key] to Summarize

Dan

stj1120

Hi @danilang

Yes, there are dummy values '#' in the Affiliate Plant key column. It is not only for New Zealand, it is present for most of the countries. # values tells us that there is no plant.

Could you check and help me how do I get the exact data as Validation2 after I add Affiliate Plant Key column?

Thank you.

Best,

Teja.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703