Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
lark9820
Contributor II
Contributor II

ApplyMap ERROR field NOT FOUND

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

Labels (1)
1 Solution

Accepted Solutions
anthonyj
Creator III
Creator III

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

View solution in original post

2 Replies
anthonyj
Creator III
Creator III

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
Contributor II
Contributor II
Author

Thanks Anthony, I appreciate your detailed explanation. it works