topic Re: New calculated field from 2 different tables in script in App Development

New calculated field from 2 different tables in script

pascaldijkshoor — Wed, 27 Jun 2018 10:58:37 GMT

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?

Re: New calculated field from 2 different tables in script

sunny_talwar — Wed, 27 Jun 2018 11:04:19 GMT

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

Re: New calculated field from 2 different tables in script

pascaldijkshoor — Wed, 27 Jun 2018 11:33:44 GMT

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

Re: New calculated field from 2 different tables in script

BalaBhaskar_Qlik — Wed, 27 Jun 2018 12:00:31 GMT

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;

Re: New calculated field from 2 different tables in script

pascaldijkshoor — Wed, 27 Jun 2018 12:20:43 GMT

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?

Re: New calculated field from 2 different tables in script

BalaBhaskar_Qlik — Wed, 27 Jun 2018 12:31:08 GMT

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

Re: New calculated field from 2 different tables in script

pascaldijkshoor — Wed, 27 Jun 2018 12:59:40 GMT

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

Re: New calculated field from 2 different tables in script

pascaldijkshoor — Wed, 27 Jun 2018 13:00:52 GMT

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;

Re: New calculated field from 2 different tables in script

pascaldijkshoor — Wed, 27 Jun 2018 13:51:13 GMT

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