Join the Alteryx Community’s Maveryx Summer Cup event! Compete, network with others, and earn your gold through a series of challenges from July 24th to August 11th. Learn more about the event here.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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