topic Merge a table in QlikView

Merge a table

Pedro_Rodriguez — Fri, 25 Oct 2019 11:35:32 GMT

Hi all,

I have someting like:

Id, Asc1, Asc2

1, 1,

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

Re: Merge a table

sunny_talwar — Fri, 25 Oct 2019 11:57:11 GMT

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;