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

Alteryx Designer Desktop Discussions

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

Flattening a Table

R_L
7 - Meteor

Hello,

 

I currently have the following table, which I want to flatten to have a single record.

 

As you can see in the below table, I have multiple records which are essentially the same. This is because I have cross-tabbed Name and Value columns which were originally fields.

 

Currently:

 

IDNameValueIDID_1ID_2
ABCD12345Full NameBobF23  
ABCD12345Address123 York StreetF23  
ABCD12345Date Created2019-01-01F23  
ABCD12345Amount22F23  
ABCD12345Full NameBob G123 
ABCD12345Address123 York Street G123 
ABCD12345Date Created2019-01-01 G123 
ABCD12345Amount22 G123 
ABCD12345Full NameBob  J12
ABCD12345Address123 York Street  J12
ABCD12345Date Created2019-01-01  J12
ABCD12345Amount22  J12

 

Goal:

IDNameValueIDID_1ID_2
ABCD12345Full NameBobF23G123J12
ABCD12345Address123 York StreetF23G123J12
ABCD12345Date Created2019-01-01F23G123J12
ABCD12345Amount22F23G123J12

 

Any help as to where I should start?

5 REPLIES 5
AbhilashR
15 - Aurora
15 - Aurora

Hi @R_L, would this solution work in your case? 

AbhilashR_0-1588305910773.png

Please mark this post as a solution accepted if it addresses your ask.

R_L
7 - Meteor

Actually, I have discovered that my particular dataset does have some unique values for IDs. I've revised the table below - but the goal is still the same:

 

IDNameValueIDID_1ID_2
ABCD12345Full NameBobF23  
ABCD12345Address123 York StreetF23  
ABCD12345Date Created2019-01-01F23  
ABCD12345Amount22F23  
ABCD12345Full NameBob G123 
ABCD12345Address123 York Street G123 
ABCD12345Date Created2019-01-01 G123 
ABCD12345Amount22 G123 
ABCD12345Full NameBob  J12
ABCD12345Address123 York Street  J12
ABCD12345Date Created2019-01-01  J12
ABCD12345Amount22  J12
ABCD12345Address123 York StreetF23  
ABCD12345Date Created2019-01-01F23  
ABCD12345Amount22F23  
ABCD12345Full NameBob G123 
ABCD12345Address123 York Street G123 
ABCD12345Date Created2019-01-01 G123 
ABCD12345Amount22 G123 
ABCD12345Full NameBob  T10
ABCD12345Address123 York Street  T10
ABCD12345Date Created2019-01-01  T10
ABCD12345Amount22  T10

 

Goal:

IDNameValueIDID_1ID_2
ABCD12345Full NameBobF23G123J12
ABCD12345Address123 York StreetF23G123J12
ABCD12345Date Created2019-01-01F23G123J12
ABCD12345Amount22F23G123J12
ABCD12345Full NameBobF23G123T10
ABCD12345Address123 York StreetF23G123T10
ABCD12345Date Created2019-01-01F23G123T10
ABCD12345Amount22F23G123T10

 

Based on the solution provided above, the J12 and T10 become concatenated instead...

R_L
7 - Meteor

Hi @AbhilashR ,

 

What if there are multiple ID_2 for the same ID? I'm seeing concatinating for the below results between J12 and V44

For example:

 

IDNameValueIDID_1ID_2ID_3ID_4
ABCD12345Full NameBobF23    
ABCD12345Address123 York StreetF23    
ABCD12345Full NameBob G123   
ABCD12345Address123 York Street G123   
ABCD12345Full NameBob  J12  
ABCD12345Address123 York Street  J12  
ABCD12345Address123 York StreetF23    
ABCD12345Full NameBob G123   
ABCD12345Address123 York Street G123   
ABCD12345Full NameBob  V44  
ABCD12345Address123 York Street  V44  
ABCD88888Full NameBob   T33 
ABCD88888Address123 York Street   T33 
AbhilashR
15 - Aurora
15 - Aurora

Hi @R_L, broadly the same set of tools would have worked in the newer dataset you provided, we just needed to update the columns selected in the Tile tool. I have attached a modified version of my original solution and below are screenshots of the output they give.

Original data output:

AbhilashR_0-1588384184181.png

Modified data output:

AbhilashR_1-1588384197257.png

Sorry for the delay in getting back to you. I hope the approach makes sense, but please let me know if a walkthrough would be beneficial.

 

ponraj
13 - Pulsar

Here is the sample workflow. Hope this is helpful. 

 

Capture.PNG

Labels
Top Solution Authors