Flattening a Table

[R_L]

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?

[AbhilashR]

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

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

[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

[AbhilashR · Accepted solution]

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.

[ponraj · Accepted solution]

Here is the sample workflow. Hope this is helpful.