Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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;
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
see my reply above, you can do this in same way
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
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
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
];