Append a column to the output

[stj1120]

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. 

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

[danilang · Accepted solution]

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

[stj1120]

Hi @danilang 

Thank you for your prompt solution..

But, when I also add Affiliate Plant key in the Join tool of the Data check container it gives some unjoined records in both left and right joins. It means max_Afiliate Plant Key which we used by grouping the country in the second summarize tool is giving the incorrect results. 

I also tried by keeping Group by instead of max but, no luck. How do we get the right data as Validation2 at XX output file? Please see the below screenshot.  

We should make sure to have all the records joined in the inner join and get the same number.

Please check and help.

Best,

Teja.

[danilang · Accepted solution]

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