Get multiple records in a field that match a value

[natalia_rodrigues]

Hi Guys,

I would like to know if there is a function, similar to apply map, but that uses more than one  condition to find a value in a table.

I have a reference table called RESSOURCE and a main table called MAIN, like this:

RESSOURCE:

NAME | RATE | START DATE | END DATE

-----------------------------------------------------

MAIN:

NAME| DATE |HOURS

MAIN has multiple rows with worked hours for a ressource and I want to know, how much that work costed. For that reason I want to create a field EUR that would be the result of HOURS (from MAIN) and RATE (from RESSOURCE) in my MAIN table.

The thing is, one ressource has multiple rates in RESSOURCE, with a start date and end date. I need to take the RATE, which has start and end date matching field DATE in MAIN.

For exemple: if I want to know how much costed John's work in 15.04.2019:

NAME| DATE |HOURS

John | 15.04.2019| 8h

I need to find a row in my table RESSOURCE, with the rate that was valid in that day. So if I have for John these 3 possibilities in RESSOURCE, my expression will return the rate in second position:

NAME | RATE | START DATE | END DATE

John  | 25€ | 01.01.2019 | 31.03.2019

John  | 40€ | 01.04.2019 | 31.07.2019

John  | 40€ | 01.08.2019 | 31.10.2019

Thant means that if my function finds the name John in RESSOURCE, but the SART DATE and END DATE dont match  DATE in MAIN, it has to keep searching until find a row that the 3 fields correspond or return a default value indicating that my conditions could be satisfied (there is no rate for that ressource within that period of time).

Can somebody help me?:)

[dplr-rn]

You need to join the tables using interval match function

basically interval match matches your date field in main to appropriate row in Resource based on start and end date (between the dates)

check below links for an overview

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

https://community.qlik.com/t5/QlikView-Documents/Interval-Match-Feature-Function/ta-p/1477919

[natalia_rodrigues]

Hi dilipranjith,

I think this is very close to what need, but I need to match not only the interval with Date, but also the ressource name. There will be a lot of intervals that will match for a date in MAIN, but just the one that also matches the ressource name should be returned. Have you any idea how can I work it around?

Thank you for your contribution!

[dplr-rn]

that would need a few additional steps

see below

MAIN:
load * Inline [
NAME, DATE ,HOURS

John , 15/04/2019, 8
];

RESOURCE:
load * inline [
NAME , RATE , START DATE , END DATE

John , 25 , 01/01/2019 , 31/03/2019

John , 40 , 01/04/2019 , 31/07/2019

John , 40 , 01/08/2019 , 31/10/2019
];

//interval match on date and name

ResourceNew:
IntervalMatch (DATE,NAME)
Load distinct [START DATE], [END DATE], NAME resident RESOURCE;

//you will get multiple synthetic keys and circular references

// to avoid that load the  rate into the table  created with interval match

left Join(ResourceNew)
load * resident RESOURCE;
drop table RESOURCE;

[natalia_rodrigues]

My ResourceNew is then empty (look screenshot)

[dplr-rn]

check the format of the dates.
for the sake of ease locally i changed 01.01.2019 to 01/01/2019
change it back to what is defined in your app

[natalia_rodrigues]

You were right, the date format was a problem, but still not that. 😞

I changed the data a little bit, but the script is the same:

MAIN:
load * inline
[
NAME, DATE ,HOURS
John , 15.04.2019, 5
John , 15.01.2019, 10
Maria,  15.01.2019, 10
];

RESOURCE:
load * inline
[
NAME , RATE , START_DATE , END_DATE
John , 10 , 01.01.2019 , 31.03.2019
John , 20 , 01.04.2019 , 31.07.2019
];

ResourceNew:
IntervalMatch (DATE,NAME)
Load distinct START_DATE, END_DATE, NAME resident RESOURCE;
//you will get multiple synthetic keys and circular references
// to avoid that load the  rate into the table  created with interval match
left Join(ResourceNew)
load * resident RESOURCE;
drop table RESOURCE;

Then I changed the code like this (NAME and DATE by  IntervalMatch are switched):

MAIN:
load * inline
[
NAME, DATE ,HOURS
John , 15.04.2019, 5
John , 15.01.2019, 10
Maria,  15.01.2019, 10
];

RESOURCE:
load * inline
[
NAME , RATE , START_DATE , END_DATE
John , 10 , 01.01.2019 , 31.03.2019
John , 20 , 01.04.2019 , 31.07.2019
];

left Join
ResourceNew:
IntervalMatch (NAME, DATE)
load Distinct START_DATE, END_DATE, NAME resident RESOURCE;
left Join(MAIN)
LOAD * Resident RESOURCE;
DROP Table RESOURCE;

And I got this table, which is actually not matching the date with the interval, but ist very similar with what I need:

 

[dplr-rn]

fyi the left join i did is not on Table Main

[dplr-rn]

result screenshot

[natalia_rodrigues]

Thats exactaly what I want!! But with your precious code I got two tables (MAIN and ResourceNew) with a synthetic key. How did you get that?