Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am facing strange issue while applying transformations in Qlkview using apply map command in qv 12.1 version.
Following is my script
FACT_RAW:
// Record Keys
load
Unique_Mail_Id,
IF(ComputerName() = 'HOFGA1059', Unique_Item_Id, AutoNumber(Unique_Item_Id)) AS Unique_Item_Id,
NUM(RecNo()) AS RECORD_NUMBER,
SCVER_UPDATED_DTTM,
Pcl_Identity_Id,
if(len(rtrim(ltrim(Unique_Mail_Id))) > 0 , 1, 0) as Unique_Mail_Id_Count,
// MPER Data
Mailpiece_Count,
Item_Weight,
Price_Paid,
Mail_Piece_Volume,
Current_Status,
Omc_Scan_Type,
Imc_Scan_Type,
Do_Scan_Type,
TEXT(APPLYMAP('SCAN_TYPE_MAPPING', Omc_Scan_Type, 'Undefined')) AS Omc_Scan_Type_Desc,
TEXT(APPLYMAP('SCAN_TYPE_MAPPING', Imc_Scan_Type, 'Undefined')) AS Imc_Scan_Type_Desc,
Omc_Manual_Scan_Type,
Imc_Manual_Scan_Type,
Doorstep_Manual_Input_Type,
NUM(IF(Eo_Scan_Out_Dt < (Eo_Scan_In_Dt + 19), 1, IF(Eo_Scan_Out_Dt = (Eo_Scan_In_Dt + 19), 2, IF(Eo_Scan_Out_Dt > (Eo_Scan_In_Dt + 19), 3, 0)))) AS RTS_Ind,
NUM(ALT(Damaged_Flag, 0) + ALT(Overweight_Flag, 0) + ALT(Oversize_Flag, 0) + ALT(Address_Incomplete_Flag, 0)) AS OutOfSpecAttributes,
// Pre-Advice Data
Pre_Adv_Weight,
Pre_Adv_Volume,
Pre_Adv_Customer,
Pre_Adv_Account_Num,
TEXT(APPLYMAP('CUSTOMER_MAPPING', TEXT(Pre_Adv_Account_Num), NULL())) AS Customer_Name,
TEXT(APPLYMAP('MAIL_CLASS_MAPPING', Pre_Adv_Mail_Class, 'Undefined')) AS Pre_Adv_Mail_Class_Group,
// Pre-Advice or MPER
TEXT(APPLYMAP('MAIL_CLASS_MAPPING', IF(Mail_Class > 0, Mail_Class, Pre_Adv_Mail_Class), 'Undefined')) AS Final_Mail_Class_Group,
TEXT(APPLYMAP('MAIL_FORMAT_MAPPING', IF(Mail_Format > 0, Mail_Format, Pre_Adv_Mail_Format), 'Undefined')) AS Final_Mail_Format_Group,
IF(Channel_ID > 0, Channel_ID, Pre_Adv_Channel_Id) AS Final_Channel_Id,
TEXT(LOOKUP('CHANNEL_DESC_T', 'CHANNEL_ID_T', IF(Channel_ID > 0, Channel_ID, Pre_Adv_Channel_Id), 'CHANNEL_MAPPING_T')) AS Final_Channel_Desc,
TEXT(LOOKUP('CHANNEL_GROUP_T', 'CHANNEL_ID_T', IF(Channel_ID > 0, Channel_ID, Pre_Adv_Channel_Id), 'CHANNEL_MAPPING_T')) AS Final_Channel_Group,
TEXT(LOOKUP('PRODUCT_CD_T', 'ITEM_ID_T', (IF(Item_Id > 0, Item_Id, Pre_Adv_Item_Id)), 'PRODUCT_MAPPING_T')) AS Final_Product_Cd,
TEXT(LOOKUP('PRODUCT_DESC_T', 'ITEM_ID_T', (IF(Item_Id > 0, Item_Id, Pre_Adv_Item_Id)), 'PRODUCT_MAPPING_T')) AS Final_Product_Desc,
TEXT(LOOKUP('PRODUCT_LVL1_T', 'ITEM_ID_T', (IF(Item_Id > 0, Item_Id, Pre_Adv_Item_Id)), 'PRODUCT_MAPPING_T')) AS Final_Product_Lvl1,
TEXT(LOOKUP('PRODUCT_LVL2_T', 'ITEM_ID_T', (IF(Item_Id > 0, Item_Id, Pre_Adv_Item_Id)), 'PRODUCT_MAPPING_T')) AS Final_Product_Lvl2,
TEXT(LOOKUP('PRODUCT_LVL3_T', 'ITEM_ID_T', (IF(Item_Id > 0, Item_Id, Pre_Adv_Item_Id)), 'PRODUCT_MAPPING_T')) AS Final_Product_Lvl3,
TEXT(LOOKUP('PRODUCT_LVL4_T', 'ITEM_ID_T', (IF(Item_Id > 0, Item_Id, Pre_Adv_Item_Id)), 'PRODUCT_MAPPING_T')) AS Final_Product_Lvl4,
NUM(LOOKUP('PRE_SORTED_PRODUCT_T', 'ITEM_ID_T', NUM(IF(ISNULL(Item_Id), Pre_Adv_Item_Id, Item_Id)), 'PRODUCT_MAPPING_T')) AS Pre_Sorted_Product,
NUM(IF(ISNULL(Item_Weight), Pre_Adv_Weight, Item_Weight)) AS Final_Item_Weight,
NUM(IF(ISNULL(Mail_Piece_Volume), Pre_Adv_Volume, Mail_Piece_Volume)) AS Final_Mail_Piece_Volume,
// Delivery Data
//Delivery_Postcode,
IF(ISNULL(Delivery_Postcode), 'UNKNOWN', Delivery_Postcode) AS Delivery_Postcode, //Defect 2384 / 2341 - Map Null Values to "UNKNOWN"
IF(NOT ISNULL(LOOKUP('Country_Name', 'Country_Cd_2', Delivery_Country, 'COUNTRY_CODE_LOOKUP')), LOOKUP('Country_Name', 'Country_Cd_2', Delivery_Country, 'COUNTRY_CODE_LOOKUP'),
IF(NOT ISNULL(LOOKUP('Country_Name', 'Country_Cd_3', Delivery_Country, 'COUNTRY_CODE_LOOKUP')), LOOKUP('Country_Name', 'Country_Cd_3', Delivery_Country, 'COUNTRY_CODE_LOOKUP'), 'UNKNOWN')) AS Delivery_Country, //Defect 2384
IF(NOT ISNULL(LOOKUP('Country_Zone', 'Country_Cd_2', Delivery_Country, 'COUNTRY_CODE_LOOKUP')), LOOKUP('Country_Zone', 'Country_Cd_2', Delivery_Country, 'COUNTRY_CODE_LOOKUP'),
IF(NOT ISNULL(LOOKUP('Country_Zone', 'Country_Cd_3', Delivery_Country, 'COUNTRY_CODE_LOOKUP')), LOOKUP('Country_Zone', 'Country_Cd_3', Delivery_Country, 'COUNTRY_CODE_LOOKUP'), 'UNKNOWN')) AS Delivery_Country_Zone, //Defect 2384
Expected_Walk,
TEXT(IF(ISNUM(RIGHT(LEFT(Sender_Postcode, 2),1)), LEFT(Sender_Postcode, 1), LEFT(Sender_Postcode, 2))) AS Sender_Postcode,
Sender_Postcode AS Sender_Postcode_Full,
TEXT(IF(ISNUM(RIGHT(LEFT(Delivery_Postcode, 2),1)), LEFT(Delivery_Postcode, 1), LEFT(Delivery_Postcode, 2))) AS Delivery_Area,
LOOKUP('Exp_Do_PostCode_Zone', 'Exp_Do_PostCode_Area', TEXT(IF(ISNUM(RIGHT(LEFT(Delivery_Postcode, 2),1)), LEFT(Delivery_Postcode, 1), LEFT (Delivery_Postcode, 2))), 'Expected_DO_Locations') AS Delivery_Zone,
// Flags
Pre_Adv_Flag,
Omc_Scan_Flag,
Imc_Scan_Flag,
Do_Scan_Flag,
Doorstep_Scan_Flag,
Eo_Scan_In_Flag,
Eo_Scan_Out_Flag,
Missort_Flag,
Damaged_Flag,
Late_Delivery_Flag,
Failed_Delivery_Flag,
Closed_Flag,
Oversize_Flag,
Overweight_Flag,
Address_Incomplete_Flag,
RTS_Flag,
Pre_Adv_Late_Ind,
IF(ISNULL(Unique_Mail_Id), 1, 0) AS Missing_Mail_Id_Flag,
IF(Expected_Delivery_Dt_Com = Doorstep_Scan_Dt, 1, 0) AS Delivery_Match_Flag,
IF(Pre_Adv_Volume = Mail_Piece_Volume, 1, 0) AS Size_Match_Flag,
IF(Pre_Adv_Weight = Item_Weight, 1, 0) AS Weight_Match_Flag,
Comm_FTD_Flag,
Duplicate_Event_Flag,
Duplicate_Event_Count, //count of duplicate events - not strictly a flag
Intl_Flag,
Pre_Adv_Prod_Qual_Flg,
Mper_Prod_Qual_Flg,
Pre_Adv_Del_Ontime_Flg,
Mper_Del_Ontime_Flg,
// Event Codes
Omc_Event_Cd,
Imc_Event_Cd,
Do_Event_Cd,
Eo_In_Event_Cd,
Eo_Out_Event_Cd,
Doorstep_Scan_Cd,
Duplicate_Event_Cd,
// Event Code Descriptions
TEXT(APPLYMAP('EVENT_CODE_MAPPING', Omc_Event_Cd, 'Undefined')) AS Omc_Event_Desc,
TEXT(APPLYMAP('EVENT_CODE_MAPPING', Imc_Event_Cd, 'Undefined')) AS Imc_Event_Desc,
TEXT(APPLYMAP('EVENT_CODE_MAPPING', Do_Event_Cd, 'Undefined')) AS Do_Event_Desc,
TEXT(APPLYMAP('EVENT_CODE_MAPPING', Eo_In_Event_Cd, 'Undefined')) AS Eo_In_Event_Desc,
TEXT(APPLYMAP('EVENT_CODE_MAPPING', Eo_Out_Event_Cd, 'Undefined')) AS Eo_Out_Event_Desc,
TEXT(APPLYMAP('EVENT_CODE_MAPPING', Doorstep_Scan_Cd, 'Undefined')) AS Doorstep_Scan_Desc,
// Event Code Groups
TEXT(APPLYMAP('EVENT_CODE_GROUP_MAPPING', Doorstep_Scan_Cd, 'Undefined')) AS Doorstep_Scan_Group,
TEXT(APPLYMAP('EVENT_CODE_GROUP_MAPPING', Eo_In_Event_Cd, 'Undefined')) AS Eo_In_Event_Group,
TEXT(APPLYMAP('EVENT_CODE_GROUP_MAPPING', Eo_Out_Event_Cd, 'Undefined')) AS Eo_Out_Event_Group,
// Location Data
IF(ISNULL(LOOKUP('OMC_NAME_T', 'OMC_LOCATION_ID_T', Pre_Adv_Expected_Omc, 'OMC_MAPPING_T')), 0, Pre_Adv_Expected_Omc) AS Pre_Adv_Expected_Omc,
IF(ISNULL(LOOKUP('OMC_NAME_T', 'OMC_LOCATION_ID_T', Omc_Id, 'OMC_MAPPING_T')), 0, Omc_Id) AS Omc_Id,
IF(ISNULL(LOOKUP('IMC_NAME_T', 'IMC_LOCATION_ID_T', Expected_Imc, 'IMC_MAPPING_T')), 0, Expected_Imc) AS Expected_Imc,
IF(ISNULL(Imc_Id) AND Missort_Flag = 1 AND Missort_Location_Type = 'DO' AND NOT ISNULL(Do_Id),
ALT(LOOKUP('DO_PARENT_IMC_ID_T', 'DO_LOCATION_ID_T', Do_Id, 'DO_MAPPING_T'), 0),
IF(ISNULL(LOOKUP('IMC_NAME_T', 'IMC_LOCATION_ID_T', Imc_Id, 'IMC_MAPPING_T')), 0, Imc_Id)) AS Imc_Id,
IF(ISNULL(LOOKUP('DO_NAME_T', 'DO_LOCATION_ID_T', Do_Final_Loc_Id, 'DO_MAPPING_T')), 0, Do_Final_Loc_Id) AS Expected_Do,
IF(ISNULL(LOOKUP('DO_NAME_T', 'DO_LOCATION_ID_T', Do_Id, 'DO_MAPPING_T')), 0, Do_Id) AS Do_Id,
IF(ISNULL(LOOKUP('EO_NAME_T', 'EO_LOCATION_ID_T', Eo_Location_Cd, 'EO_MAPPING_T')), 0, Eo_Location_Cd) AS Eo_Location_Cd,
IF(ISNULL(LOOKUP('POL_NAME_T', 'POL_LOCATION_ID_T', Pol_Location_Id, 'POL_MAPPING_T')), 0, Pol_Location_Id) AS Pol_Location_Id,
IF(ISNULL(LOOKUP('NAME_T', 'LOCATION_ID_T', Damaged_Location, 'LOCATION_MAPPING_T')), 0, Damaged_Location) AS Damaged_Location,
Damaged_Location_Type,
// Dates and Times
DATE(Omc_Scan_Dt) AS Omc_Scan_Dt,
DATE(Imc_Scan_Dt) AS Imc_Scan_Dt,
DATE(Do_Scan_Dt) AS Do_Scan_Dt,
DATE(Doorstep_Scan_Dt) AS Doorstep_Scan_Dt,
DATE(Eo_Scan_In_Dt) AS Eo_Scan_In_Dt,
DATE(Eo_Scan_Out_Dt) AS Eo_Scan_Out_Dt,
DATE(Damaged_Dt) AS Damaged_Dt,
DATE(Expected_Delivery_Dt_Com) AS Expected_Delivery_Dt,
DATE(Pre_Adv_Dt) AS Pre_Adv_Dt,
DATE(Pre_Adv_Exp_Omc_Scan_Dt) AS Pre_Adv_Exp_Omc_Scan_Dt,
DATE(FLOOR(Duplicate_Scan_Dttm), 'DD/MM/YYYY') AS Duplicate_Scan_Dt,
DATE(Pre_Adv_Expected_Delivery_Dt) AS Pre_Adv_Expected_Delivery_Dt,
DATE(Comm_Del_Dt) AS Delivered_Dt, //CR258 / Defect 2426
DATE(RANGEMIN(Omc_Scan_Dt, Imc_Scan_Dt, Do_Scan_Dt, Doorstep_Scan_Dt, Eo_Scan_In_Dt, Eo_Scan_Out_Dt)) AS First_Seen_Dt,
DATE(RANGEMAX(Omc_Scan_Dt, Imc_Scan_Dt, Do_Scan_Dt, Doorstep_Scan_Dt, Eo_Scan_In_Dt, Eo_Scan_Out_Dt)) AS Last_Seen_Dt,
DATE(RANGEMIN(Doorstep_Scan_Dt, Eo_Scan_In_Dt, Eo_Scan_Out_Dt)) AS Delivery_First_Seen_Dt,
WEEKDAY(Omc_Scan_Dt) AS Omc_Scan_Day,
WEEKDAY(Imc_Scan_Dt) AS Imc_Scan_Day,
WEEKDAY(Do_Scan_Dt) AS Do_Scan_Day,
WEEKDAY(Doorstep_Scan_Dt) AS Doorstep_Scan_Day,
WEEKDAY(Eo_Scan_In_Dt) AS Eo_Scan_In_Day,
WEEKDAY(Eo_Scan_Out_Dt) AS Eo_Scan_Out_Day,
WEEKDAY(Expected_Delivery_Dt_Com) AS Expected_Delivery_Day,
WEEKDAY(Damaged_Dt) AS Damaged_Day,
WEEKDAY(Pre_Adv_Dt) AS Pre_Adv_Day,
WEEKDAY(Pre_Adv_Exp_Omc_Scan_Dt) AS Pre_Adv_Exp_Omc_Scan_Day,
WEEKDAY(Omc_Scan_Dt) & ' ' & HOUR(Omc_Scan_Time) AS Omc_Scan_Day_Hr,
WEEKDAY(Imc_Scan_Dt) & ' ' & HOUR(Imc_Scan_Time) AS Imc_Scan_Day_Hr,
WEEKDAY(Duplicate_Scan_Dttm) AS Duplicate_Scan_Day,
WEEKDAY(Pre_Adv_Expected_Delivery_Dt) AS Pre_Adv_Expected_Delivery_Day,
TIME(Duplicate_Scan_Dttm, 'hh:mm:ss') AS Duplicate_Scan_Time,
// Date interval calculations
ALT(INTERVAL(Omc_Scan_Dt - Pre_Adv_Exp_Omc_Scan_Dt, 'D'), -99) AS Omc_Pre_Adv_Omc_Diff,
ALT(INTERVAL(Imc_Scan_Dt - Pre_Adv_Exp_Omc_Scan_Dt, 'D'), -99) AS Imc_Pre_Adv_Omc_Diff,
ALT(INTERVAL(Doorstep_Scan_Dt - Expected_Delivery_Dt_Com, 'D'), -99) AS Doorstep_Expected_Delivery_Diff,
ALT(INTERVAL(Doorstep_Scan_Dt - Pre_Adv_Expected_Delivery_Dt_Com, 'D'), -99) AS Pre_Adv_Expected_Delivery_Diff,
ALT(INTERVAL(Omc_Scan_Dt - Doorstep_Scan_Dt, 'D'), -99) AS Omc_Doorstep_Diff,
ALT(INTERVAL(Imc_Scan_Dt - Doorstep_Scan_Dt, 'D'), -99) AS Imc_Doorstep_Diff,
// Expected OMC scan date calculated from workplan
DATE(Doorstep_Scan_Dt - APPLYMAP('WORKPLAN_MAPPING', APPLYMAP('MAIL_CLASS_MAPPING', Mail_Class) & TEXT(WEEKDAY(Doorstep_Scan_Dt)))) AS Omc_Workplan_Exp_Scan_Dt,
// Create Processing Fields
$(vGetProcessingField(1, 1, 0)) AS Processing_Scan_Flag,
DATE($(vGetProcessingField(Omc_Scan_Dt, Imc_Scan_Dt, NULL()))) AS Processing_Scan_Dt,
HOUR($(vGetProcessingField(Omc_Scan_Time, Imc_Scan_Time, NULL()))) AS Processing_Scan_Hr,
$(vGetProcessingField(Omc_Scan_Id, Imc_Scan_Id, NULL())) AS Processing_Scan_Id,
$(vGetProcessingField(Omc_Event_Cd, Imc_Event_Cd, NULL())) AS Processing_Event_Cd,
TEXT(APPLYMAP('EVENT_CODE_MAPPING', $(vGetProcessingField(Omc_Event_Cd, Imc_Event_Cd, NULL())), 'Undefined')) AS Processing_Event_Desc,
IF(ISNULL(LOOKUP('OMC_NAME_T', 'OMC_LOCATION_ID_T', $(vGetProcessingField(Omc_Id, Imc_Id, NULL())), 'OMC_MAPPING_T')), 0, $(vGetProcessingField(Omc_Id, Imc_Id, NULL()))) AS Processing_Id,
$(vGetProcessingField('OMC', 'IMC', NULL())) AS Processing_Location_Type,
// Ops Fields for FTD Calculation - CR258
// DATE(RANGEMIN(IF(NOT ISNULL(Doorstep_Scan_Cd) AND MATCH(Doorstep_Scan_Cd, $(vDeliveredCodes)) > 0, Doorstep_Scan_Dt, NULL()),
// IF(NOT ISNULL(Eo_In_Event_Cd) AND MATCH(Eo_In_Event_Cd, $(vDeliveredCodes)) > 0, Eo_Scan_In_Dt, NULL()),
// IF(NOT ISNULL(Eo_Out_Event_Cd) AND MATCH(Eo_Out_Event_Cd, $(vDeliveredCodes)) > 0, Eo_Scan_Out_Dt, NULL()))) AS Delivered_Dt,
Prod_Qual_Fail_Desc
FROM
[..\4_QVDs\FACT\DAILY\V_PCL_ANA_REP_DAILY.qvd](qvd);
Can any one guide how to solve this ?
Vikas
You have use mapping function in your script
but you have to define mapping table for below tables
SCAN_TYPE_MAPPING
CUSTOMER_MAPPING
MAIL_CLASS_MAPPING
MAIL_FORMAT_MAPPING
EVENT_CODE_MAPPING
EVENT_CODE_GROUP_MAPPING
WORKPLAN_MAPPING
from QlikView help
The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:
applymap('mapname', expr [ , defaultexpr ] )
where:
mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.
expr is the expression, the result of which should be mapped.
defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.
Examples:
// Assume the following mapping table:
map1:
mapping load * inline [
x, y
1, one
2, two
3, three ] ;
ApplyMap ('map1', 2 ) returns ' two'
ApplyMap ('map1', 4 ) returns 4
ApplyMap ('map1', 5, 'xxx') returns 'xxx'
ApplyMap ('map1', 1, 'xxx') returns 'one'
ApplyMap ('map1', 5, null( ) ) returns NULL
ApplyMap ('map1', 3, null( ) ) returns 'three'
and also
You need to define mapping tables
SCAN_TYPE_MAPPING
CUSTOMER_MAPPING
MAIL_CLASS_MAPPING
MAIL_FORMAT_MAPPING
EVENT_CODE_MAPPING
EVENT_CODE_GROUP_MAPPING
WORKPLAN_MAPPING
e.g.
EVENT_CODE_MAPPING:
Mapping Load
...
From ...
;
Note: In previous version it was accepting without mapping table
You please explain in details what is EVENT_CODE_MAPPING & how to implement the same
Vikas
You have use mapping function in your script
but you have to define mapping table for below tables
SCAN_TYPE_MAPPING
CUSTOMER_MAPPING
MAIL_CLASS_MAPPING
MAIL_FORMAT_MAPPING
EVENT_CODE_MAPPING
EVENT_CODE_GROUP_MAPPING
WORKPLAN_MAPPING
from QlikView help
The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:
applymap('mapname', expr [ , defaultexpr ] )
where:
mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.
expr is the expression, the result of which should be mapped.
defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.
Examples:
// Assume the following mapping table:
map1:
mapping load * inline [
x, y
1, one
2, two
3, three ] ;
ApplyMap ('map1', 2 ) returns ' two'
ApplyMap ('map1', 4 ) returns 4
ApplyMap ('map1', 5, 'xxx') returns 'xxx'
ApplyMap ('map1', 1, 'xxx') returns 'one'
ApplyMap ('map1', 5, null( ) ) returns NULL
ApplyMap ('map1', 3, null( ) ) returns 'three'
and also
Yes that is already defined in previous tab still i am getting issues ?
vikas
Share some sample data.
Thanks WORKPLAN_MAPPING was missing in my script added and working for me now.
vikas
Now actually i got there is bug in version 11.2 then
Vikas