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_2ABCD12345Full 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 J12ABCD12345Address123 York Street J12ABCD12345Date Created2019-01-01 J12ABCD12345Amount22 J12 Goal: IDNameValueIDID_1ID_2ABCD12345Full NameBobF23G123J12ABCD12345Address123 York StreetF23G123J12ABCD12345Date Created2019-01-01F23G123J12ABCD12345Amount22F23G123J12 Any help as to where I should start?

Flattening a Table

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:

ID	Name	Value	ID	ID_1	ID_2
ABCD12345	Full Name	Bob	F23
ABCD12345	Address	123 York Street	F23
ABCD12345	Date Created	2019-01-01	F23
ABCD12345	Amount	22	F23
ABCD12345	Full Name	Bob		G123
ABCD12345	Address	123 York Street		G123
ABCD12345	Date Created	2019-01-01		G123
ABCD12345	Amount	22		G123
ABCD12345	Full Name	Bob			J12
ABCD12345	Address	123 York Street			J12
ABCD12345	Date Created	2019-01-01			J12
ABCD12345	Amount	22			J12

Goal:

ID	Name	Value	ID	ID_1	ID_2
ABCD12345	Full Name	Bob	F23	G123	J12
ABCD12345	Address	123 York Street	F23	G123	J12
ABCD12345	Date Created	2019-01-01	F23	G123	J12
ABCD12345	Amount	22	F23	G123	J12

Any help as to where I should start?

Help

Accepted answers

AbhilashR

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:

Modified data output:

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.

sample_updated.yxmd

ponraj

Here is the sample workflow. Hope this is helpful.

Flattening a Table.yxmd

All comments

AbhilashR

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

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

sample.yxmd

R_L

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:

ID	Name	Value	ID	ID_1	ID_2
ABCD12345	Full Name	Bob	F23
ABCD12345	Address	123 York Street	F23
ABCD12345	Date Created	2019-01-01	F23
ABCD12345	Amount	22	F23
ABCD12345	Full Name	Bob		G123
ABCD12345	Address	123 York Street		G123
ABCD12345	Date Created	2019-01-01		G123
ABCD12345	Amount	22		G123
ABCD12345	Full Name	Bob			J12
ABCD12345	Address	123 York Street			J12
ABCD12345	Date Created	2019-01-01			J12
ABCD12345	Amount	22			J12
ABCD12345	Address	123 York Street	F23
ABCD12345	Date Created	2019-01-01	F23
ABCD12345	Amount	22	F23
ABCD12345	Full Name	Bob		G123
ABCD12345	Address	123 York Street		G123
ABCD12345	Date Created	2019-01-01		G123
ABCD12345	Amount	22		G123
ABCD12345	Full Name	Bob			T10
ABCD12345	Address	123 York Street			T10
ABCD12345	Date Created	2019-01-01			T10
ABCD12345	Amount	22			T10

Goal:

ID	Name	Value	ID	ID_1	ID_2
ABCD12345	Full Name	Bob	F23	G123	J12
ABCD12345	Address	123 York Street	F23	G123	J12
ABCD12345	Date Created	2019-01-01	F23	G123	J12
ABCD12345	Amount	22	F23	G123	J12
ABCD12345	Full Name	Bob	F23	G123	T10
ABCD12345	Address	123 York Street	F23	G123	T10
ABCD12345	Date Created	2019-01-01	F23	G123	T10
ABCD12345	Amount	22	F23	G123	T10

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

R_L

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:

ID	Name	Value	ID	ID_1	ID_2	ID_3	ID_4
ABCD12345	Full Name	Bob	F23
ABCD12345	Address	123 York Street	F23
ABCD12345	Full Name	Bob		G123
ABCD12345	Address	123 York Street		G123
ABCD12345	Full Name	Bob			J12
ABCD12345	Address	123 York Street			J12
ABCD12345	Address	123 York Street	F23
ABCD12345	Full Name	Bob		G123
ABCD12345	Address	123 York Street		G123
ABCD12345	Full Name	Bob			V44
ABCD12345	Address	123 York Street			V44
ABCD88888	Full Name	Bob				T33
ABCD88888	Address	123 York Street				T33

ponraj

Here is the sample workflow. Hope this is helpful.

Flattening a Table.yxmd