topic Re: Use Table as List for Another Table in QlikView

Use Table as List for Another Table

Lisa2 — Sat, 16 Nov 2024 01:36:04 GMT

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!

Re: Use Table as List for Another Table

Brett_Bleess — Thu, 02 Jan 2020 22:39:58 GMT

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

Re: Use Table as List for Another Table

Kushal_Chawda — Thu, 02 Jan 2020 22:53:52 GMT

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;

Re: Use Table as List for Another Table

Lisa2 — Thu, 02 Jan 2020 22:54:00 GMT

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

Re: Use Table as List for Another Table

Kushal_Chawda — Thu, 02 Jan 2020 22:59:45 GMT

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

Re: Use Table as List for Another Table

subbareddykm — Fri, 03 Jan 2020 03:14:40 GMT

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

Re: Use Table as List for Another Table

Brett_Bleess — Mon, 06 Jan 2020 13:05:45 GMT

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

Re: Use Table as List for Another Table

Saravanan_Desingh — Mon, 27 Apr 2020 23:33:46 GMT

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 ];