Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;