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: 
Lisa2
Contributor
Contributor

Use Table as List for Another Table

Hello, I need to use the values in Table A to find the values in Table B. Wherever they exist in Table B, change the data in another column (in Table B) to be zero.  I am not sure how to approach this.  If anyone could point me in the right direction.  Thanks!

Labels (3)
7 Replies
Brett_Bleess
Former Employee
Former Employee

Hey Lisa, I suspect what will really help on this one is if you can provide some sample data for your two Tables and your expected output appearance, that should help the folks capable give you a much better answer right from the start.

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Kushal_Chawda

If I understood correctly. Take this simple example.

Here, exists condition in TableB checks the values of ID from TableA with ID1 of TableB. If ID1 values matches with ID 0 will be assigned else Qty1 column  values will remain as it is. In simple words newly created column Qty2 will have 0 values wherever ID1 values matches with ID value of TableA.

TableA:
load ID,
Qty
FROM TableA;

TableB:
load ID1,
Qty1,
if(Exists(ID,ID1),0,Qty1) as Qty2
FROM TableB;

Lisa2
Contributor
Contributor
Author

Using list of values from Table A, find them in Table B and zero out the Hours.

Table A:

Items
1653-46
3256-98
8523-14
7892-92
3152-35

 

Table B:

Items	Hours
1653-46	0
1653-56	12
3256-98	0
8523-14	0
5648-87	10
7892-92	0
3152-35	0
7559-78	15
7325-19	18

 

Kushal_Chawda

see my reply above, you can do this in same way

subbareddykm
Creator II
Creator II

Try this :

 

T1:
LOAD *,1 as Flag;
LOAD * INLINE [
Items
1653-46
3256-98
8523-14
7892-92
3152-35
];
Join
T2:

LOAD * INLINE [
Items, Hours
1653-46, 5
1653-56, 12
3256-98, 6
8523-14, 3
5648-87, 10
7892-92, 3
3152-35, 9
7559-78, 15
7325-19, 18
];

Final:

LOAD *,
If(Flag=1,0,Hours) as NewField
Resident T1;

DROP Table T1;
DROP Fields Flag;

 

Hope this helps

Brett_Bleess
Former Employee
Former Employee

Lisa, if either of the posts you have received did help with your use case, be sure to return to your thread and use the Accept as Solution button on any posts that were useful to get your use case working, as this gives the posters credit for the help, and lets the other Community Members know what actually worked, so please be sure to do this when you can.  If you are still working upon things, and you need further assistance, please leave another update on where you are currently stuck with things.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Saravanan_Desingh

One more solution.

Mapper:
Mapping
LOAD *,0 INLINE [
    Items
    1653-46
    3256-98
    8523-14
    7892-92
    3152-35
];

tab1:
LOAD Items, ApplyMap('Mapper',Items,Hours) As Hours INLINE [
    Items, Hours
    1653-46, 5
    1653-56, 12
    3256-98, 6
    8523-14, 3
    5648-87, 10
    7892-92, 3
    3152-35, 9
    7559-78, 15
    7325-19, 18
];

commQV21.PNG