Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
natalia_rodrigues
Contributor III
Contributor III

Get multiple records in a field that match a value

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?:)

1 Solution

Accepted Solutions
dilipranjith
Partner
Partner

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

View solution in original post

14 Replies
dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

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!
dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

My ResourceNew is then empty (look screenshot)Unbenannt.PNG

dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

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;
Unbenannt3.PNG
 
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:Unbenannt3.PNG
 
dilipranjith
Partner
Partner

fyi the left join i did is not on Table Main
dilipranjith
Partner
Partner

result screenshotCapture.PNG

natalia_rodrigues
Contributor III
Contributor III
Author

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?