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
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

Apply Map - Field Not Found

My script is below. When I try and run this I get an error that HourInterval is not found when it gets to the ApplyMap part. Am I doing something wrong?

 

 

HourMap:
Mapping Load * Inline [
ProcessEndHour, HourInterval
0, '12:00 am - 1:00 am'
1, '1:00 am - 2:00 am'
2, '2:00 am - 3:00 am'
3, '3:00 am - 4:00 am'
4, '4:00 am - 5:00 am'
5, '5:00 am - 6:00 am'
6, '6:00 am - 7:00 am'
7, '7:00 am - 8:00 am'
8, '8:00 am - 9:00 am'
9, '9:00 am - 10:00 am'
10, '10:00 am - 11:00 am'
11, '11:00 am - 12:00 pm'
12, '12:00 pm - 1:00 pm'
13, '1:00 pm - 2:00 pm'
14, '2:00 pm - 3:00 pm'
15, '3:00 pm - 4:00 pm'
16, '4:00 pm - 5:00 pm'
17, '5:00 pm - 6:00 pm'
18, '6:00 pm - 7:00 pm'
19, '7:00 pm - 8:00 pm'
20, '8:00 pm - 9:00 pm'
21, '9:00 pm - 10:00 pm'
22, '10:00 pm - 11:00 pm'
23, '11:00 pm - 12:00 pm'
]
;


RPACash:
Load MasterRecordID,
CashSystem,
OutputRecordID,
CashRecordID,
PaymentType,
DepositDate,
Dual(WeekStart(DepositDate)&' - '&WeekEnd(DepositDate), WeekStart(DepositDate)) AS WeekDates,
CheckWireNumber,
CashAmount,
RemitterName,
ProducerCode,
PolicyNumber,
AuditNumber,
ClosedFlag,
PolicySystem_ModCode,
PolicySystem_ProducerCode,
PolicySystem_PolicyNumber,
PolicySystem_TransEffDate,
PolicySystem_InsuredNameCode,
PolicySystem_LedgerCde,
PolicySystem_InvoiceNumber,
PolicySystem_AuditNumber,
PolicySystem_OpenAmount,
ProcessAmount,
ProcessCategory,
ProcessCode,
Process,
RPAStatus,
// OutputMsg,
BotCategory,
OCRStatus,
ProcessStartDtTime,
ProcessEndDtTime,
ApplyMap('HourMap', HourInterval, ProcessEndHour) AS ProcessEndHour,
Date(Floor(ProcessEndDtTime)) AS ProcessEndDt,
DataExtStatus,
CashClearingStatus,
CashAppStatus,
CashProcessStatus,
ManualCashFlg,
DataExtractSuccess,
OCRSuccessCount,
DESuccessCount
FROM [lib://CSSC_Cash_QVD_Folder/RPACash.qvd](qvd);

1 Solution

Accepted Solutions
Taoufiq_Zarra

ApplyMap('HourMap', ProcessEndHour, 'Null') AS HourInterval,
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

8 Replies
Taoufiq_Zarra

@greend21 

First the Script :

 

in ApplyMap('HourMap', HourInterval, ProcessEndHour) AS ProcessEndHour,

ProcessEndHour : this field does not exist yet

you can do ApplyMap('HourMap', HourInterval, 'Null') AS ProcessEndHour,

or

ApplyMap('HourMap', HourInterval, 'Null') AS ProcessEndHour,

 

Mapping

And I don't see the field you're trying to map here?

can you share a sample data to share with you the correct mapping

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
greend21
Creator III
Creator III
Author

I should have gone into more detail. ProcessEndHour does already exist in RPACash.qvd which is being generated by SQL. It is just the hour from the timestamp so 1,2,14,17, etc. With the the apply map I am trying to say if there is a mapping I missed,then just show the hour number. The error message I receive is that HourInterval does not exist even though I have that in my mapping load.

 

Since ProcessEndHour does exist in my qvd that is being loaded, this is being used as the Key field between the qvd and the mapping table. I want to replace 1 with '1:00 am - 2:00 am' and so on.

martinpohl
Partner - Master
Partner - Master

does HourInterval  exists in qvd also.

applymap does:

use the mapid

compare the value from the first column in mapping table with the field that is given

takes the third value or the original value if not defined

so it compares the mapping values with field HourInterval (from your qvd)

Regards

Taoufiq_Zarra

if I understood correctly.
You need :

ApplyMap('HourMap', HourInterval, 'Null') AS ProcessEndHour,


as long as HourInterval exist in RPACash.qvd

values with Null are unmapped values

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
greend21
Creator III
Creator III
Author

HourInterval does not exist in RPACash.qvd. ProcessEndHour does. HourInterval is what I need mapped to the table.

greend21
Creator III
Creator III
Author

No, HourInterval does not exist in the qvd. It is being created in the mapping load and is the field I need mapped. It should be looking at ProcessEndHour from the mapping  and the RPACash table and replacing ProcessEndHour from the QVD with the HourInterval from the mapping table. If no match is found then I have it set to use ProcessEndHour from the qvd. From what I can see I am following the directions as provided on the help page.

 

 

Taoufiq_Zarra

ApplyMap('HourMap', ProcessEndHour, 'Null') AS HourInterval,
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
greend21
Creator III
Creator III
Author

I see what I was doing wrong. I was putting the field I want in the ApplyMap function instead of the field it is joining on. Thanks! I missed that from the help section.