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]

exactly my code.

theres still 1 synthetic key. but thats fine as far as a model is concerned.

Synthetic keys by themselves are not bad (though often its a sign of bad data model) see this blig post

in this case the relationship pretty straight forward and clean; because its 2 columns common qlik creates a synthetic key efficiently.

[natalia_rodrigues]

When I use your code, I lost "Maria" because there is no RATE for her. But I still need her in my data set:

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, ARG1, ARG2
John , 10 , 01.01.2019 , 31.03.2019, a, b
John , 20 , 01.04.2019 , 31.07.2019, c, d
Maria , 20 , 01.04.2019 , 31.07.2019, c, d
];
//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;

[dplr-rn · Accepted solution]

from the screenshot it looks as if you are doing a qualify operation. (column name is ResourceNew.Name)

as i dont know what you have done ; hard to comment.

attached is the qvf file

[natalia_rodrigues]

I created a new app from zero and copy your code! It worked!!!
Thanks a loooooot!!! s2

[dplr-rn]

no prob. happy to help