topic Re: Using Apply Map only for values that exist in Mapping Table in QlikView

Using Apply Map only for values that exist in Mapping Table

olromanenko — Wed, 09 Nov 2022 16:46:14 GMT

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!

Re: Using Apply Map only for values that exist in Mapping Table

BrunPierre — Fri, 11 Nov 2022 09:03:09 GMT

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

Re: Using Apply Map only for values that exist in Mapping Table

marcus_sommer — Fri, 11 Nov 2022 11:40:26 GMT

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