Using Apply Map only for values that exist in Mapping Table

[olromanenko]

Hi everyone,

I want to paste new values into the table, but only if they exist in the Mapping Table.

The main table is:

ID	field1	filed2	some_other_important_fields
1	-	-	1234
2	asd	fgh	2356
3	-	-	7654
4	yxc	vbn	3456
5	-	-	6789

Than I want to paste some neu values from another table, that looks like:

ID	new_field1	new_field2
1	jkl	opü
3	qwe	rtz

So I use mapping:

Map:

mapping load

ID,

new_field1&'|'&new_field2 as new_values

resident table2;

new_main_table:

Load

ID,

Subfield(ApplyMap('Map',ID),'|',1) as field1,

Subfield(ApplyMap('Map',ID),'|',2) as field2,

some_other_important_fields

resident main_table;

drop main table;

The problem is, that wenn I do so, the whole field1 and field 2 will overwrite. The desired result:

ID	field1	filed2	some_other_important_fields
1	jkl	opü	1234
2	asd	fgh	2356
3	qwe	rtz	7654
4	yxc	vbn	3456
5	-	-	6789

Schoud I use some If condition? Or maybe there are some other ways, how I can paste this values into the table? Because my main table actualy have a loooot of columns and I do not really want to overwrite it. Only if it´s the only way.

Any help is greatly appreciated!

[BrunPierre]

Field1Map:
Mapping LOAD ID, 
             new_field1
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/Using-Apply-Map-only-for-values-that-exist-in-Mapping-Table/td-p/2002578]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);

Field2Map:
Mapping LOAD ID, 
             new_field2
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/Using-Apply-Map-only-for-values-that-exist-in-Mapping-Table/td-p/2002578]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);


new_main_table:
LOAD ID, 
     some_other_important_fields,
     ApplyMap('Field1Map',ID,field1) as Field1,
     ApplyMap('Field2Map',ID,filed2) as Field2

FROM
[https://community.qlik.com/t5/QlikView-App-Dev/Using-Apply-Map-only-for-values-that-exist-in-Mapping-Table/td-p/2002578]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

[marcus_sommer]

You need to query the exists of any respectively a certain content of the fields before you apply the applymap(). This may be done with multiple if-loops or depending on your data just with:

coalesce(field1, Subfield(ApplyMap('Map',ID),'|',1)) as field1,

- Marcus