Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

UNIQUE IDENTIFIER AND MERGING OF COLUMN DATA

Lauren_Holmes
8 - Asteroid

Hello, 

I am stuck on a current use case and am unsure on the best way to approach this.

I have a large data set that contains unique identifiers. From this I am determining some separate output files however I need to try and combine the data as best as possible at the very beginning. 

 

Currently I join column 1 with column 2 to get column 3. This works for the most part but when there are two unique identifiers that match and that have the same "type" in column 1 I need to combine them together. For below the desired output would be in green:

 

EXAMPLE:

UNIQUE IDENTIFIERCOLUMN 1COLUMN 2COLUMN 3
1234PM12PM 12
1234PM12PM 12
1234  PM 6

 

Column 2 is always a 12 month data type.

However column 2 will not always be 12 - there could be a scenario where column 2 contains "6" (6 months) and "12" (12 months) and the desired output would be "4" to stand for 4 months as the event "PM" happens 3 times throughout the year. 

 

UNIQUE IDENTIFIERCOLUMN 1COLUMN 2COLUMN 3DESIRED OUTCOME
1234PM12PM 12 
1234PM12PM 12PM 6
1234CAL6CAL 6CAL 6

 

Any suggestions on how the best way is to do this would be greatly appreciated!! 

6 REPLIES 6
Qiu
20 - Arcturus
20 - Arcturus

@Lauren_Holmes 
Honestly, I am a bit confused.

Can you just give a simple input and an output so we can figure out how?

Lauren_Holmes
8 - Asteroid

Hello - 

 

EXAMPLE 

INPUT

1234PM12
1234PM12
1234CAL12

 

OUTPUT

1234PM 6
1234CAL 12

 

As 1234 are the same on each line and PM is shown twice these need to be combined. I did have an idea of concatenating so the output would be:

1234PM,PM
1234CAL

 

and from here I can use a text input to say if concatenated column = PM, PM then output is PM 6? I know how to concatenate all eg. group by unique identifier and concatenate the Column 1 however issue with this is I don't want to concatenate all - PM,PM,CAL i only want to concatenate those that have identical events in column 1.  

Qiu
20 - Arcturus
20 - Arcturus

@Lauren_Holmes 
This is based on my interpretion. 😁

0521-Lauren_Holmes.PNG

atcodedog05
22 - Nova
22 - Nova

Hi @Lauren_Holmes 

 


@Lauren_Holmes wrote:

 

and from here I can use a text input to say if concatenated column = PM, PM then output is PM 6? I know how to concatenate all eg. group by unique identifier and concatenate the Column 1 however issue with this is I don't want to concatenate all - PM,PM,CAL i only want to concatenate those that have identical events in column 1.  


Based on what i could understand you want to concat identical events. So what you need to do is groupby identical events & conact identical events

 

Workflow:

atcodedog05_0-1621597348710.png

 

If you refer my workflow i am doing groubpy ID, Identical event, concat of Identical event, count of Identical event & group by value.

 

In the formula tool i am diving values/count and getting 6.

 

Hope this helps 🙂

 

Lauren_Holmes
8 - Asteroid

This is great, thanks so much 🙂

Lauren_Holmes
8 - Asteroid

Exactly what i am after, thank you so much 🙂

Labels