We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Table filling

jpscoralick
8 - Asteroid

Hello everyone!

I have the following table (1).

KEYPERCCODEREFERENCE
POI89371150FIJ001
POI89372150FIJ002
POI89371203FIJ003
POI89372203FIJ004

Table 1

I have to fill fields REF1 and REF2 in order to table 2 become table 3.

KEYPERCREF1REF2
POI89371  
POI89372  

Table 2

 

KEYPERCREF1REF2
POI89371FIJ001FIJ003
POI89372FIJ002FIJ004

Table 3

I've tried different approaches, but I still can't get how to do it.
Any ideas?
Thanks in advance!

8 REPLIES 8
cjaneczko
13 - Pulsar

Cross tab gets the data in the format of Table 3. Do you have more than 2 Codes?

cjaneczko_0-1682451001832.png

 

RobertOdera
13 - Pulsar

Hi, @jpscoralick 

 

Kindly consider the below.

I hope you find it helpful - cheers!

 

RobertOdera_0-1682451226300.png

 

DanielG
12 - Quasar

@jpscoralick  see attached. This uses the cross-tab mentioned above, but also dynamically creates column names Ref1,Ref2, etc.  

DanielG
12 - Quasar

Best practice to sort data before the multi-row tool as well.  I forgot it the first time.  Added it here with all columns sorted since it is just a four-row set.  Configure as needed for your full real data.  😁

jpscoralick
8 - Asteroid

Thanks a lot @cjaneczko@RobertOdera and @DanielG for the quick and useful answers!

 

If you all could, please, help me again...
Actually, table 1 and table 2 are two different Excel files. So, I need to fill table 2 with data from table 1, generating the results showed on table 3.
I understood that CrossTab it's the key of my solution, but how can I do it with dealing with data from 2 different Excel files?

RobertOdera
13 - Pulsar

Sure thing, @jpscoralick 

 

If your files have the same schema:

 

1. Bring them in using the Input Tool(s)

2. Stack them (append) using the Union Tool

3. the rest should flow per @DanielG  solution

 

If your files have different schemas, kindly provide a sample(s) so that we can offer treatments for you to consider - Cheers!

 

Btw, I'm signing off until tomorrow, but I'm sure you will get help before then 😎

jpscoralick
8 - Asteroid

Thanks a lot @RobertOdera!
Really appreciate your kind and useful attention. It solved my needs.
Best regards

RobertOdera
13 - Pulsar

You're most welcome @jpscoralick!

Labels
Top Solution Authors