We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

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

How to compress data from 2 records into one?

iudogie
6 - Meteoroid

My source data has 3 columns:

  • id
  • value
  • gid

 

This is an example of how my source data looks based on one id:

id

value

gid

5555555

Cookies

pastry

5555555

Daniel

chef

 

 

I want to create 2 new fields based on this source data:

  • pastry type
  • chef name

Here is the logic I want to use to create these fields:

  • pastry type
    • If [gid] = 'pastry' then [value]
  • chef name
    • If [gid] = 'chef' than [value]

 

This is how my output data is looking now:

id

value

gid

pastry type

chef name

5555555

Cookies

pastry

Cookies

 

5555555

Daniel

chef

 

Daniel

 

My goal is to compress these two records into one record so it looks like this:

 

id

value

gid

pastry type

chef name

5555555

Cookies

pastry

Cookies

Daniel

 

The only fields I need in the output data are these:

id

pastry type

chef name

5555555

Cookies

Daniel

 

What's the best way to do this? I know I can join back to the source table multiple times to get this result but I am trying to avoid that.

 

Thank you

2 REPLIES 2
ncrlelia
11 - Bolide

Hi @iudogie,

 

It seems like the required output has [gid] as the column headers and the [value] are their respective values.

You can:

  1. Apply your formula to update [gid] instead of creating new fields
    • IF [gid] = 'pastry' THEN 'pastry type'
      ELSEIF [gid] = 'chef' THEN 'chef name'
      ELSE '' ENDIF
    • ncrlelia_0-1631065790655.png

       

  2. Cross tab the dataset, with [gid] as the columns and [Value] as the values
    • ncrlelia_2-1631065849134.png

       

  3. Replace '_' in the column names with a Dynamic Rename tool

Please see attached my suggested solution.

Hope it helps.

 

Cheers,

Lelia

 

iudogie
6 - Meteoroid

Thank you! This was very helpful!

Labels