Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I want to add a new calculated field in the script from 2 tables that are linked with the 'main' table. This is the current situation in my script:
Main Table:
ShipmentNumber
Load_Location
Unload_Location
ExcelFileLoad
Load_Location
Load_Location_New
ExcelFileUnload
Unload_Location
Uload_Location_New
The Excel files are created to get the correct load/unload locations into Qlik. I want to create a new calculated field with the following formula: "Load_Location_New"&'-'&"Unload_Location_New" as Shipment_Lane.
How could I fix this?
Would you be able to share a sample with expected output?
If this is what you mean, the new table would look like the small table below. First I had a calculated field in the main table based on the Load_Location and Unload_Location, but since the locations in these fields are not always correct, i have made an Excel file that changes the wrong location names into the correct location names.
ShipmentNumber | Load_Location_New | Unload_Location_New | ShipmentLane |
385473 | AMS | MPX | AMS - MPX |
983483 | CDG | BRU | CDG - BRU |
934939 | LHR | BCN | LHR - BCN |
394933 | BRU | LHR | BRU - LHR |
May be like this:
Main_Table:
Load
ShipmentNumber,
Load_Location,
Unload_Location
From ABC;
ExcelFileLoad:
Load
Load_Location,
Load_Location_New
From BCD;
MapFile_Load:
Mapping Load
Load_Location,
Load_Location_New
Resident ExcelFileLoad;
ExcelFileUnload:
Load
Unload_Location,
Unload_Location_New
From CDE;
MapFile_Unload:
Mapping Load
Unload_Location,
Unload_Location_New
Resident ExcelFileUnload;
Temp_Main1:
applymap('ExcelFileLoad',Load_Location,Null()) as Loadlocnew,
applymap('ExcelFileUnload',Unload_Location,Null()) as Unloadlocnew;
Concatenate(Main_Table)
Temp2:
Loadlocnew&'_'&Unloadlocnew as ShipmentLane
Resident Temp_Main1;
Drop Table Temp_Main1;
Thanks for you reply, haven't tried the script yet but I have got a question. Why do you use the MapFile_Load/Unload tables? Because they are the same as the ExcelFile tables and are not used further in the script?
For clarity, i created like that. Some times these tables may have more columns.
I tried the script but i'm getting the following error at the Temp_Main1 table: ApplyMap error: map_id not found
Also I added a Load statement under the Temp_Main1, so the script i used is:
Temp_Main1:
Load
applymap('ExcelFileLoad',Load_Location,null()) as Loadlocnew,
applymap('ExcelFileUnload',Unload_Location,null())as Unloadocnew;
I did some research to the applymap function and adjusted some parts and now it's fixed. Thanks a lot