ApplyMap ERROR field NOT FOUND

[lark9820]

Hi Qlik Masters, 

If I comment out the “ if (("Case Type" = 'Movie Star')' and "Movie Star" = 'Andy Lau' and EntityName LIKE ('*Entertainment PTY LTD*')),'Movie_HV','') as test_HV,“, it works perfectly.

The below script shows as  EntityName not FOUND error . However, I really need the test_HV column in my project, how should I change the EntityName field to ensure the below script working?

Entity:

mapping LOAD

    Case_Number as "Case ID",

    Entity_Names

FROM [lib://REPORT_QVD/EAC.qvd] (qvd);

Case:

LOAD

                "Case ID",

                "Date Started",

                "Case Type",

                "Movie Star",

                ApplyMap('Entity',"Case ID",'') as EntityName,

                if (("Case Type" = 'Movie Star' and "Movie Star" = 'Andy Lau' and EntityName LIKE ('*Hongkong Entertainment PTY LTD*')),'Movie_HV','') as test_HV,

FROM [lib://REPORT_QVD/Case.qvd] (qvd) WHERE "Date Started" >='1/1/2016' and Exists(Source);

[anthonyj · Accepted solution]

Hi @lark9820 ,

In Qlik as in SQL we aren't able to use a new column in the same step that creates it so you have two options here. You can use a preceding load to create the column first and then use it, or you can put the applymap inside your "if" condition.

Preceding Load Option:

Case:
LOAD
*,
if (("Case Type" = 'Movie Star' and "Movie Star" = 'Andy Lau' and EntityName LIKE ('*Hongkong Entertainment PTY LTD*')),'Movie_HV','') as test_HV
;

LOAD

"Case ID",

"Date Started",

"Case Type",

"Movie Star",

ApplyMap('Entity',"Case ID",'') as EntityName

FROM [lib://REPORT_QVD/Case.qvd] (qvd) WHERE "Date Started" >='1/1/2016' and Exists(Source);

OR

Use applymap in your "if" condition

Case:

LOAD

"Case ID",

"Date Started",

"Case Type",

"Movie Star",

ApplyMap('Entity',"Case ID",'') as EntityName,

if (("Case Type" = 'Movie Star' and "Movie Star" = 'Andy Lau' and ApplyMap('Entity',"Case ID",'') LIKE ('*Hongkong Entertainment PTY LTD*')),'Movie_HV','') as test_HV
FROM [lib://REPORT_QVD/Case.qvd] (qvd) WHERE "Date Started" >='1/1/2016' and Exists(Source);

I hope this helps.

Thanks
Anthony

[lark9820]

Thanks Anthony, I appreciate your detailed explanation. it works