Chart expressions for retrieving values linked to a single record selected by date.

[MarkH1]

Hi all,

I am working on a Qlik sense saas application about employment agencies. I have a table with employers, a table with job openings, a table with workers (employees) and also a table with matches. A match links a single job opening to a single worker. A worker can have many matches and a job can have many contracted workers. 

A match has a start date and an end date. The end date can be empty (NULL).

What I need to do is add some fields on both the workers table and the employers table. These are:

The total number of matches a person was ever involved in;

The end date of the most recent match (the match with the newest start date), if any;

The status of the most recent match. If the end date is empty the status is 'open', if a person never was involved in a match the status is 'never'. Otherwise, 'closed'

Of course the values of the fields are the same for each employer/worker, regardless of selections or dimensional values.

I tried to add the fields in the data source application but failed, the queries became too complex and slow. Perhaps it is possible in the data load script, I don't know.

I pretty sure that it is possible to add these field in the front-end, that is, as chart expressions, but I am new to Qlik and have no idea how to do it. I appreciate your help!

Here is some matches example data, the last three columns contain the values I am looking for for the workers.

[canerkan]

Hi MarkH1,

this seems possible in the Data Load Editor. Do you have any sample Data of the tables you are loading? I would recommend calculating the fields in your loadscript and adding them to your Employee Table and then simply display them in your visualization, like a straight table. That way the performance of your App the does not decrease.

Regards,

Can

[MarkH1]

Hi Can,

I agree, doing this in the data load script this would be the best way. Below is some sample data; Both tables have ore columns but these are not important form my issue:

Match table:
WorkerID;EmployerID;Startdate;Enddate
15;1648;2016-11-15;2020-1106
15;2820;2019-05-24;2021-09-20
4141;113;2013-03-19;2013-04-12
4141;115;2021-09-21;NULL
4141;2473;2013-09-06;2015-02-04
4141;2891;2019-02-01;2020-03-09

Worker table
WorkerID;Name
15;John
84;Mark
4141;Jane

cheers, Mark

[canerkan]

Hi MarkH1,

I'm sorry for my late reply but i've been laying sick in bed due to covid the last weeks. I'll look into this and come back at you.

Regards

Can

[canerkan · Accepted solution]

Hi MarkH1,

you can find my Script for the Data Load Editor below or check attached .qvf.

Match_table:
Load * Inline [
WorkerID;EmployerID;Startdate;Enddate
15;1648;2016-11-15;2020-11-06
15;2820;2019-05-24;2021-09-20
4141;113;2013-03-19;2013-04-12
4141;115;2021-09-21
4141;2473;2013-09-06;2015-02-04
4141;2891;2019-02-01;2020-03-09
]
(delimiter is ';');
 
tmpWorker:
Load * Inline [
WorkerID;Name
15;John
84;Mark
4141;Jane
]
(delimiter is ';');

Left Join(tmpWorker)
Load
	WorkerID,
    EmployerID as Employer,
    WorkerID & '_' & EmployerID as JoinKey,		//To Join Enddate of Lat match for each Employer, can be dropped later
    Count(WorkerID) as TotalMatches
Resident Match_table
Group by WorkerID, EmployerID;

Left Join(tmpWorker)
Load
	WorkerID & '_' & EmployerID as JoinKey,
    Max(Date(Enddate)) as EnddateLastMatch
Resident Match_table
Group by WorkerID & '_' & EmployerID;

Worker:
Load
	WorkerID,
    Name,
    Employer,
    TotalMatches,
    EnddateLastMatch,
    If(TotalMatches > 0 and IsNull(EnddateLastMatch) = 0, 'closed', If(TotalMatches > 0 and IsNull(EnddateLastMatch) , 'open', 'never')) as StatusLastMatch
Resident tmpWorker;
Drop Table tmpWorker;

 Let me know if it helped.

Regards,

Can

[MarkH1]

Hi Can,

Hope you recoverd well! Thanks for your answer, in the meantime I managed to solve my issue in the data source application. But I tested also with your script, it works perfect, thanks!

Cheers, Mark

[canerkan]

Hi Mark,

i'm doing well again, thanks! And if you were able to solve this on your own, even better 😁

Regards,

Can