Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
achakilam1022
Creator II
Creator II

Join and update tables in QlikSense

Hi,

I have two tables. Please help me in updating the field values while joining them

A:

load * inline[

id, field1, field2

1,0,0

2,0,0

3,0,0

4,0,0

5,1,0

];

B:

load * inline[

id, field1, field2

1,1,0

2,1,1

3,0,1

6,1,0

];

Question: Update table A by checking if the `id` from A is present in B or not(similar to left join). If present in B, update the field1 and field2 values of that id in A by picking its corresponding values from table B

Required Output is (modify table A as:)

id, field1, field2

1,1,0

2,1,1

3,0,1

4,0,0

5,1,0

I appreciate any help with this.

Thanks,

Amuktha

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Amuktha,

may be this

B:
Load * inline [
id, field1, field2
1,1,0
2,1,1
3,0,1
6,1,0]
;
A:
NoConcatenate
load id,If(Exists(id),Lookup('field1','id',id,'B'),field1) as field1,
If(Exists(id),Lookup('field2','id',id,'B'),field2) as field2
inline [
id, field1, field2
1,0,0
2,0,0
3,0,0
4,0,0
5,1,0]
;
Drop Table B;

Regards,

Antonio

View solution in original post

4 Replies
antoniotiman
Master III
Master III

Hi Amuktha,

may be this

B:
Load * inline [
id, field1, field2
1,1,0
2,1,1
3,0,1
6,1,0]
;
A:
NoConcatenate
load id,If(Exists(id),Lookup('field1','id',id,'B'),field1) as field1,
If(Exists(id),Lookup('field2','id',id,'B'),field2) as field2
inline [
id, field1, field2
1,0,0
2,0,0
3,0,0
4,0,0
5,1,0]
;
Drop Table B;

Regards,

Antonio

achakilam1022
Creator II
Creator II
Author

Hi Antonio,

Thanks for looking into this. Following script returns all field1 and field2 values as is from A and not updating them.

B:

Load id, field 1 as field1, field 2  as field2

FROM [.....xlsx]

(ooxml, embedded labels, table is Sheet1);

A:

Load id, If(Exists(id),Lookup('field1','id',id,'B'),field1) as field1,

If(Exists(id),Lookup('field2','id',id,'B'),field2) as field2

from [lib://.....qvd](qvd);

May I know what am I doing wrong. I appreciate your help.

Thanks,

Amuktha

antoniotiman
Master III
Master III

See Attachment

achakilam1022
Creator II
Creator II
Author

There was some problem with my data set. The solution works perfect.

Thanks for your help.