topic Re: Apply Map replace Field Values in QlikView

Apply Map replace Field Values

qlikwiz123 — Sat, 16 Nov 2024 00:27:52 GMT

Table A:

Name	ID
A	1
B
C	5
D
E	7

Table B:

Name	ID
A	20
B	2
C	99
D	9
E	7
F	8
I	10
J	11
K	15

Result:

Name	ID
A	20
B	2
C	99
D	9
E	7

I want to replace and fill the null ID values from Table B in Table A.

NameIdMap:
Mapping Load Name, ID
From TableB

Table_A:
Load Name,
if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID
From TableA

I tried this but this does not replace ID values in TableA with the values in Table B

Re: Apply Map replace Field Values

lironbaram — Wed, 17 Jun 2020 17:42:21 GMT

in your result table you replaced existing values of ID , with values from the mapping table
but your expression is keeping existing ID , and only fill missing , what exactly you look for
any how
if you want to replace existing and fill the field ID1 in the script below will do it ,
and if you only want to fill the missing value than ID2 is your choice

fieldmap: Mapping load * inline [ Name,ID A,20 B,2 C,99 D,9 E,7 F,8 I,10 J,11 K,15 ]; Table1: load *, Applymap('fieldmap',Name,ID) as ID1, if(ID>0,ID,Applymap('fieldmap',Name) ) as ID2; load * Inline [ Name,ID A,1 B, C,5 D, E,7 L,10 ];

Re: Apply Map replace Field Values

qlikwiz123 — Wed, 17 Jun 2020 19:00:29 GMT

Doesn't seem to be working. Returns empty values for the missing ones and does not replace the IDs either.

Re: Apply Map replace Field Values

Saravanan_Desingh — Wed, 17 Jun 2020 20:54:09 GMT

TableA's A, C & E are not NULL. Why are still replacing it from TableB? Do u want to replace always with B value?

Re: Apply Map replace Field Values

qlikwiz123 — Wed, 17 Jun 2020 21:16:58 GMT

Yes. If Table A ID is null, then fill it with values from Table B. If Table A ID has value, then it must be replaced with Table B's value. If Table A ID has value and Table B does not, then fill the value from Table A

Re: Apply Map replace Field Values

Saravanan_Desingh — Wed, 17 Jun 2020 22:00:22 GMT

Check this.

Mapper: Mapping LOAD * INLINE [ Name, ID A, 20 B, 2 C, 99 D, 9 E, 7 F, 8 I, 10 J, 11 K, 15 ]; tab1: LOAD Name, ApplyMap('Mapper',Name,ID) As ID INLINE [ Name, ID A, 1 B, C, 5 D, E, 7 Z, 88 ];

Re: Apply Map replace Field Values

Saravanan_Desingh — Wed, 17 Jun 2020 22:01:14 GMT

Output

Re: Apply Map replace Field Values

qlikwiz123 — Thu, 18 Jun 2020 13:54:30 GMT

@Saravanan_Desingh

Hi,

This fails my 3rd condition

1. If 'ID' in Table A is blank, then fill it with 'ID' from Table B

2. If 'ID' in Table A has a value, replace it with 'ID' from Table B

3. If 'ID' in Table A has a value and 'ID' in Table B is null, then use the value from table A.

Re: Apply Map replace Field Values

Saravanan_Desingh — Thu, 18 Jun 2020 14:17:21 GMT

Can u try this?

Mapper: Mapping LOAD * INLINE [ Name, ID A, 20 B, 2 C, 99 D, 9 E, F, 8 I, 10 J, 11 K, 15 ]; tab1: LOAD Name, If(Len(ApplyMap('Mapper',Name))<>0,ApplyMap('Mapper',Name,ID),ID) As ID INLINE [ Name, ID A, 1 B, C, 5 D, E, 7 Z, 88 ];

Re: Apply Map replace Field Values

Saravanan_Desingh — Thu, 18 Jun 2020 14:18:24 GMT

Re: Apply Map replace Field Values

Saravanan_Desingh — Thu, 18 Jun 2020 22:41:47 GMT

Further simplified.

Mapper: Mapping LOAD * INLINE [ Name, ID A, 20 B, 2 C, 99 D, 9 E, F, 8 I, 10 J, 11 K, 15 ]; tab1: LOAD Name, Alt(ApplyMap('Mapper',Name,ID),ID) As ID INLINE [ Name, ID A, 1 B, C, 5 D, E, 7 Z, 88 ];

Re: Apply Map replace Field Values

Saravanan_Desingh — Thu, 18 Jun 2020 22:42:30 GMT