Flattening data from three left joined tables

[leahpenelope]

Hi everyone,

I have three tables - R,S,T - which were combined using two left joins in SQL.

Table R was joined with Table S in a left join followed by Table R was joined with Table T in a left join, in one SQL query.

Let's called the resulting table, Table Z.

Table R has one common field R-S with S.

Table R has one common field R-T with T.

Tables S and T have no common fields.

Moreover, one other field in Table R - field M - tends to have duplicate values - one pointing to Table S and one pointing to Table T.

For e.g. if I were to group by field M in Table Z using the Summarize tool in Alteryx, I will see at least two rows of the same value in field M. The top row will have values in the fields from Table S and null values in the fields from Table T, and the bottom row will have values in the fields from Table T and null values in the fields from Table S.

I wish to 'flatten' these rows with duplicates of M without losing data from Tables S or T.

As Table T has more rows than Table S, likely Table S will take on null rows. See graphical illustration of the desired transformation.

Can someone help me brainstorm the best way to go about flattening the data?

Thank you

[echuong1 · Accepted solution]

It sounds like this can be accomplished with Joins and the Summarize tool.

Are you able to upload some sample data for each of your tables, as well as your goal output?

[leahpenelope]

Hello @echuong1 

Thanks for your tip.

I was able to solve using the Summarize tool.

Group by "R-M field" and Max each of "S-Name", "S-Value, "T-Name", "T-Value"

That would flatten the table.

Currently:

R-M field	S-Name	S-Value	T-Name	T-Value
1	Alice	3	null	null
1	null	null	Tim	4
2	Sharon	8	null	null
2	null	null	Bob	2
3	Lisa	6	null	null
3	null	null	Ryan	1
4	null	null	Chris	3
5	Amy	2	null	null
5	null	null	Richard	4
6	null	null	Mike	5

Goal:

R-M field	S-Name	S-Value	T-Name	T-Value
1	Alice	3	Tim	4
2	Sharon	8	Bob	2
3	Lisa	6	Ryan	1
4	null	null	Chris	3
5	Amy	2	Richard	4
6	null	null	Mike	5