Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Pedro_Rodriguez
Contributor III
Contributor III

Merge a table

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

1 Reply
sunny_talwar

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;