Apply Map - Field Not Found

[greend21]

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

[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

[greend21]

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]

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

[greend21]

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

[greend21]

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 · Accepted solution]

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

[greend21]

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.