Merge a table

[Pedro_Rodriguez]

Hi all,

I have someting like:

Id, Asc1, Asc2

1, 1,

1, 2,

1, 2,

1, 3,

1,  , 1

1, , 2

1,  ,4

2, 1,

And i want to get:

Id, Asc1, Asc2

1, 1, 1

1, 2, 2

1, 3,

1,  ,4

2, 1,

That is, every record has either Asc1 or Asc2. If Asc1 equals Asc2 for the same Id, I want those records merged, and keep the rest as they are.

Thanks a lot in advance

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD Id&'|'&If(Len(Trim(Asc1)) = 0, 0, Asc1)&'|'&If(Len(Trim(Asc2)) = 0, 0, Asc2) as Key,
	 Id,
	 If(Len(Trim(Asc1)) > 0, Asc1) as Asc1,
	 If(Len(Trim(Asc2)) > 0, Asc2) as Asc2;
LOAD * INLINE [
    Id, Asc1, Asc2
    1, 1
    1, 2
    1, 2
    1, 3
    1, , 1
    1, , 2
    1, , 4
    2, 1
];

FinalTable:
NoConcatenate
LOAD Key as Key1,
	 Id,
	 Asc1
Resident Table
Where Len(Trim(Asc1)) > 0;

Left Join (FinalTable)
LOAD Key as Key2,
	 Id,
	 Asc2 as Asc1,
	 Asc2
Resident Table
Where Len(Trim(Asc2)) > 0;

Concatenate (FinalTable)
LOAD Key,
	 Id,
	 Asc1,
	 Asc2
Resident Table
Where Not Exists(Key2, Key) and not Exists(Key1, Key); 

DROP Table Table;