Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Append a column to the output

stj1120
8 - Asteroid

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. 

5 REPLIES 5
danilang
19 - Altair
19 - Altair

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

 

danilang_0-1626172901168.png

 

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

 

Dan

 

 

stj1120
8 - Asteroid

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
19 - Altair
19 - Altair

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

danilang_0-1626177639912.png

"#" 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.

 

danilang_2-1626178088069.png

 

 

Dan

 

 

 

 

 

stj1120
8 - Asteroid

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
19 - Altair
19 - Altair

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

Labels