Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

Join two columns with one primary key including null values

syt
5 - Atom

Hi

I have one table read from Excel:

SummaryIncluded Summaries
AB
AC
AD
BE

and another table:

SummaryIncluded IDs
A1
B2
B3

How can I join these two tables like the following

SummaryIncluded SummariesIncluded IDs
AB1
 C 
 D 
BE2
  3

 

I have tried join and multiply join, but they don't work. Their output is like:

SummaryIncluded SummariesIncluded IDs
AB1
AC1
AD1
BE2
BE3

 

the_jake_tool
12 - Quasar

Hey @syt 

 

I'm sure there are many ways to do this! I tend to think in Macros! Therefore, although its not the most simple, I've built you a macro that might help your issue.

 

I've seen a lot of things like this with formatting for importing into third party softwares.

 

Let me know if it helps or if you need any clarification...also please like and accept to confirm knowledge sharing if it helps you in the right direction 🙂

 

Sincerely,

 

J

 

the_jake_tool_0-1591793650088.png

 

Thableaus
17 - Castor
17 - Castor

Hi @syt 

 

Not the easiest solution, probably there are better than mine, but here we go

 

Thableaus_0-1591793668930.png

 

- Do a regular join

- Add a Record ID to the dataset

- Transpose the colums grouping by RecordID

- Sort your dataset in a descending way starting with the column "Value". This will be useful to wipe out the repeated values using the Multi Row Formula Tool. 

- Use the Multi Row Formula Tool with the following condition:

IF [Row+1:Value] = [Value] THEN ""
ELSE [Value] ENDIF

 

That's the best way I found to get rid of these repeated values that come after.

- Use the CrossTab Tool to put your columns back the way they were.

- Use the Select Tool to order the columns. Rename them if you find necessary.

 

Workflow attached.

 

Cheers,

the_jake_tool
12 - Quasar

@Thableaus 

 

I think this is a great challenge - maybe even weekly challenge material for intermediate level participants!

 

Wouldn't you agree?

Thableaus
17 - Castor
17 - Castor

I completely agree with you @the_jake_tool!

 

 That's a great suggestion. I'd love to see different solutions for this problem - which is something I can see many Excel users looking for.

 

 

the_jake_tool
12 - Quasar

I've worked with a lot of teams who have to get their data into weird tabular format to be exported into excel in order to be used as an import into a third party software e.g., Thomson Reuters, etc. Soon they will see the light 🙂

J

syt
5 - Atom

Hi @Thableaus 

 

Thank you for your reply. But I still have one question. If I have the input data like that

SummaryIncluded Summaries
AB
AC
AD
BE

and

SummaryIncluded IDs
A1
A4
B2
B3

The output is 

Summary Included SummariesIncluded IDs
AB1
 C 
 D 
  4
   
   
BE2
  3

I don't know why there are so many empty fields which is not needed. Can you please take a look at this? Thank you so much.

 

Workflow attached

 

Best Regards

Labels