Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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 II
Creator II

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 II
Creator II

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

lark9820
Contributor II
Contributor II
Author

Thanks Anthony, I appreciate your detailed explanation. it works