topic Re: Join Tables for missing values in QlikView

Join Tables for missing values

qlikwiz123 — Sat, 16 Nov 2024 00:15:48 GMT

I have two different tables

TableA:

ID, Name, Dept

1, A, Clothes

2, B, Shoes

3, H, Utilities

6,-, Custom

Table B:

ID, Name

1, A

2, L

3, P

4, D

5, I

6, O

7, X

8, Q

9, M

10, S

How do I join Table B with Table A, to fill the missing 'Name' values in Table A, without disturbing the table structure

Output:

ID, Name, Dept

1, A, Clothes

2, B, Shoes

3, H, Utilities

6,O, Custom

Re: Join Tables for missing values

dplr-rn — Fri, 24 Jul 2020 17:07:15 GMT

you could right join or load table B without ids from tableA and then concatenate

or if you just want it as a dimension drop Name from tableA and leave Table B as dimension table

whats the end result table you are looking for?

final answer depends on that.

Re: Join Tables for missing values

qlikwiz123 — Fri, 24 Jul 2020 18:43:53 GMT

Hi,

my output should be

Output:

TableA:

ID, Name, Dept

1, A, Clothes

2, B, Shoes

3, H, Utilities

6,O, Custom

I don't want all the rows to be concatenated from TableB, only fill up the missing 'Name' values in TableA from TableB.

Re: Join Tables for missing values

dplr-rn — Fri, 24 Jul 2020 18:48:50 GMT

see sample script below

TableA: load ID, Dept ; load * inline [ ID, Name, Dept 1, A, Clothes 2, B, Shoes 3, H, Utilities 6,-, Custom ]; TableB: left join(TableA) load * inline [ ID, Name 1, A 2, B 3, H 4, D 5, I 6, O 7, X 8, Q 9, M 10, S ];

Re: Join Tables for missing values

qlikwiz123 — Fri, 24 Jul 2020 18:56:12 GMT

Hi, thank you.

But this overrides the original 'Name' values in TableA with TableB.

I only want to fill the missing 'Name' values from TableB into TableA but not replace existing values.

I attached my sample qvw

Re: Join Tables for missing values

dplr-rn — Fri, 24 Jul 2020 19:27:06 GMT

that needs a little more round about approach. you split and load your table a into 2 temporary tables. one with Name and one without (i.e. name ='-') and then perform the joins

see attached

Re: Join Tables for missing values

qlikwiz123 — Fri, 24 Jul 2020 20:08:01 GMT

Thank you. Let me look into this and get back to you