topic Re: join two tables by creating a calculated link column in App Development

join two tables by creating a calculated link column

Venthan — Wed, 08 Mar 2023 10:15:44 GMT

I have two tables with columns as below

Header_Table:
Unique_object_id,
OBJECT_TYPE,
WORK_STATE,
CONTAINER_TYPE,
CARRIER_NAME,
PORT_OF_DISCHARGE,
ETD_DATE

Rate_Table:
CARRIER_NAME,
PORT_OF_DISCHARGE,
CONTAINER_TYPE,
EFFECTIVE_DATE,
EXPIRY_DATE,
RATE

I want to link the two tables by comparing fields CARRIER_NAME,PORT_OF_DISCHARGE, CONTAINER_TYPE and comparing ETD_DATE should fall within EFFECTIVE_DATE and EXPIRY_DATE, if matched we will add the RATE to the Header_Table.

I created a link column as "CARRIER_NAME" & '|' & "PORT_OF_DISCHARGE" & '|' & 'CONTAINER_TYPE' in both the tables. Issue is I'm not able to create the link between ETD_DATE and EFFECTIVE_DATE and EXPIRY_DATE.

Is there a way to create the link based on above mentioned columns.

Re: join two tables by creating a calculated link column

henrikalmen — Wed, 08 Mar 2023 10:31:34 GMT

I think you could load your Header_Table as you have done, and then do this:

left join(Header_Table)
load * from [ratetable]
where ETD_DATE>=EFFECTIVE_DATE and ETD_DATE<=EXPIRY_DATE
;

If I have understood your issue correctly, this could probably solve it for you.

Re: join two tables by creating a calculated link column

marcus_sommer — Wed, 08 Mar 2023 11:57:27 GMT

Joins in Qlik are only possible against native fields - any kind of calculation/matching within the where-clause could not be applied.

But this doesn't mean that you couldn't match these tables. You need only to create a real date from your effective and expiring date, for example with:

...
date(EFFECTIVE_DATE + iterno() - 1) as ETD_DATE
...
while EFFECTIVE_DATE + iterno() - 1 <= EXPIRY_DATE;

which creates appropriate records for each date in between and now the join should work.

Re: join two tables by creating a calculated link column

Venthan — Thu, 09 Mar 2023 06:38:08 GMT

Rate Table:

link_column	CARRIER_NAME	PORT_OF_DISCHARGE	CONTAINER_TYPE	EFFECTIVE_DATE	EXPIRY_DATE	RATE
ABC\|INNSA\|20FT	ABC	INNSA	20FT	01/01/2022	31/12/2022	100
ABC\|INNSA\|20FT	ABC	INNSA	20FT	01/01/2023	31/12/2023	150

Header Table:

link_column	Unique_object_id	OBJECT_TYPE	WORK_STATE	CONTAINER_TYPE	CARRIER_NAME	PORT_OF_DISCHARGE	ETD_DATE
ABC\|INNSA\|20FT	1234	TR	3	20FT	ABC	INNSA	15/02/2023

Here I want link join Rate table to Header table, which has the Rate 150, i.e. ETD date of header record falls within Effective date and Expiry Date of Rate table.

I'm not able to do this via Interval match as I get circular reference. I tried with iterno() but somehow that doesn't work correctly.

Re: join two tables by creating a calculated link column

henrikalmen — Thu, 09 Mar 2023 08:02:10 GMT

I'm still not sure what you want to achieve. Those two tables will be linked via link_column if you load them both separately, but since both tables contains the fields CONTAINER_TYPE and CARRIER_NAME you would get so called synthetic keys in your data model since there will be links also between those fields.

Re: join two tables by creating a calculated link column

Venthan — Thu, 09 Mar 2023 08:12:09 GMT

Hi Henrik,

I want a report output as below,

Unique_object_id	OBJECT_TYPE	CONTAINER_TYPE	PORT_OF_DISCHARGE	CARRIER_NAME	ETD_DATE	RATE
1234	TR	20FT	INNSA	ABC	15/02/2023	150

As you mentioned, based on the link_column the tables will be linked but if I create a table chart, we will get two records. I will manage the synthetic keys by renaming the CONTAINER_TYPE and CARRIER_NAME in Rate Table

My objective here is link the two tables based on link_column and then consider the record from Rate table where ETD_Date >= EFFECTIVE_DATE and ETD_Date <= EXPIRY_DATE.

Re: join two tables by creating a calculated link column

henrikalmen — Thu, 09 Mar 2023 08:34:05 GMT

So you don't want to create your result in the data model, you want to do it in a table chart in frontend.

Try an expression instead of the ETD_DATE field:
if(ETD_DATE>=EFFECTIVE_DATE and ETD_DATE<=EXPIRY_DATE, ETD_DATE)

I'm thinking that should give NULL for the rows you want to hide, and you can exklude null values in the settings for the table.

Re: join two tables by creating a calculated link column

Venthan — Thu, 09 Mar 2023 10:03:06 GMT

Hi Henrik,

Thanks, this works.

Try an expression instead of the ETD_DATE field:
if(ETD_DATE>=EFFECTIVE_DATE and ETD_DATE<=EXPIRY_DATE, ETD_DATE)