New calculated field from 2 different tables in script

[pascaldijkshoor]

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?

[sunny_talwar]

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

[pascaldijkshoor]

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

[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]

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]

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

[pascaldijkshoor]

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]

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