Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

New calculated field from 2 different tables in script

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?

8 Replies
sunny_talwar

Would you be able to share a sample with expected output?

pascaldijkshoor
Creator
Creator
Author

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.

ShipmentNumberLoad_Location_NewUnload_Location_NewShipmentLane
385473AMSMPXAMS - MPX
983483CDGBRUCDG - BRU
934939LHRBCNLHR - BCN
394933BRULHRBRU - LHR
balabhaskarqlik

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;

pascaldijkshoor
Creator
Creator
Author

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?

balabhaskarqlik

For clarity, i created like that. Some times these tables may have more columns.

pascaldijkshoor
Creator
Creator
Author

I tried the script but i'm getting the following error at the Temp_Main1 table: ApplyMap error: map_id not found

pascaldijkshoor
Creator
Creator
Author

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;

pascaldijkshoor
Creator
Creator
Author

I did some research to the applymap function and adjusted some parts and now it's fixed. Thanks a lot