Merging rows with same ID on non-null fields

FernandaNava — Sat, 16 Nov 2024 02:29:55 GMT

I have a table in which the an ID appears several times, but for each, there's only one field that is not null:

Is there a way in which I could merge these into a single row?

PS. I built this table from another one that looked like this:

Therefore, if this could be solved more easily by changing the way the table is constructed I could do it too.

Thank you

Re: Merging rows with same ID on non-null fields

klikgevoel — Thu, 07 May 2020 12:37:56 GMT

If I understand you correctly you want one row with the Stage*Date variables, i.e.

ID	Stage1Date	Stage2Date	Stage3Date
1	01/01/2020	01/02/2020	01/03/2020

This can be achieved via Left Join prefix and Where clause.

Example

Data:

LOAD

ID,

Stage1Date

From DataSource

Where Not IsNull(Stage1Date);

Left Join(Data)

LOAD

ID,

Stage2Date

From DataSource

Where Not IsNull(Stage2Date);

etc.

See attachment for little explanation and example.