Not able to get desried output using 2 applymap function for 1 field

[Rsaiq]

Hi All,

I am trying to create a field on the basis of matching values on 2 fields as :

If field ID from table A matching with current table ID then show 4 else 0

and
If field ID2 from table B matching with current table ID2 then show  4 else 0

I am getting the result with second applymap function but not getting with 1st Applymap function

If(match(ApplyMap('A',ID,0),4),4,
If(match(ApplyMap('B',ID2,0),4),4,0)) as value

I think some issue with the syntex. Could anyone please help me to get this done.

[marcus_sommer]

Both checks return the same value of 4 - how will you differ which mapping was applied?

[Rsaiq]

Hi @marcus_sommer thanks for reply.

I have to map from both fields.So I have created scripts as below

I have created 2 mapping table as 

A:

• Mapping Load ID,
• 4 as tabl1_ value
• From XYZ

B:

• Mapping Load ID2,
• 4 as tabl2_ value
• From XYZ

And then applied applymap on current table as below

If(match(ApplyMap('A',ID,0),4),4,
If(match(ApplyMap('B',ID2,0),4),4,0)) as value

[marcus_sommer]

It's just a check if an ID exists within a certain table and flag it with 4. Both positive matches return 4. If you want to differ between them you need to adjust the return-value. Beside of this you don't need the if-match stuff else you may use:

ApplyMap('A',ID,ApplyMap('A',ID2,0)) as value

[Rsaiq]

Hi @marcus_sommer ,

Unfortunately it still not giving desired result.

I have mentioned my data and expected result in attached screenshot.

Please have a look.

Thanks in advance.

[marcus_sommer · Accepted solution]

The suggest approach will work in general and need just an adjustment to way the mapping-tables are created. By using two mapping-tables it need of course to reference to two mappings whereby both mappings could be also combined into a single table. So depending on the way the mappings are created would both approaches workable:

ApplyMap('A',ID,ApplyMap('A',ID2,0)) as value

ApplyMap('A',ID,ApplyMap('B',ID2,0)) as value