How to compress data from 2 records into one?

[iudogie]

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

[ncrlelia · Accepted solution]

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
   • 
2. Cross tab the dataset, with [gid] as the columns and [Value] as the values
   • 
3. Replace '_' in the column names with a Dynamic Rename tool

Please see attached my suggested solution.

Hope it helps.

Cheers,

Lelia

[iudogie]

Thank you! This was very helpful!