Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RogerG
Creator
Creator

interval match based on multiple fields and 2 resident tables

Hello all,

I have 2 resident tables and trying to perform an interval match on them

 

 

temp:
LOAD * INLINE [
    EMP_ID, SALES_DATE
    1, 1/15/2020
    1, 2/11/2020
    1, 3/30/2020
    2, 1/11/2020
    2, 2/11/2020
    2, 3/25/2020
    3, 1/1/2020
    3, 2/11/2020
    3, 3/27/2020
];

Buckets:
LOAD * INLINE [
   EMP_ID,RANGE1, START_DATE,END_DATE
    1, E1R1, 1/16/2020, 2/28/2020
    1, E1R2, 3/1/2020,5/1/2020
    2, E2R1, 1/3/2020, 2/15/2020
   	2, E2R2, 3/1/2020, 4/15/2020
    3, E3R1, 1/5/2020, 2/1/2020
    3, E3R2, 3/1/2020,3/15/2020
    
];
inner join
IntervalMatch(SALES_DATE, EMP_ID) Load  START_DATE,END_DATE, EMP_ID
Resident Buckets;
INNER JOIN 
load * 
resident Buckets;
drop table temp;

 

 

 

I this code should only look at the month and year of the sales date and ranges to determine which RANGE1 is assinged.  

I would hope to see 

EMP_IDSALES_DATERANGE1
1 1/15/2020E1R1
1 2/11/2020E1R1
1 3/30/2020E1R2
2 1/11/2020E2R1
2 2/11/2020E2R1
2 3/25/2020E2R2
3 1/1/2020E3R1
3 2/11/2020E3R1
3 3/27/2020

E3R2

 

However I get this (I have not programmed for Month and year yet)

EMP_IDSALES_DATERANGE1START_DATEEND_DATE
21/11/2020E2R11/3/20202/15/2020
21/11/2020E2R23/1/20204/15/2020
12/11/2020E1R11/16/20202/28/2020
22/11/2020E2R11/3/20202/15/2020
22/11/2020E2R23/1/20204/15/2020
32/11/2020E3R23/1/20203/15/2020
4 Replies
rubenmarin

Hi @RogerG, whit that code i get this:

rubenmarin_0-1610132684731.png

Which is ok based on the ranges and different from what you get¿?

In your post you expect to return E1R1 for emp1 on sales date 1/15/2020 but this date is outside the defined ranges, and that's the reason it wil not assign E1R1.

The file has a different code... I don't really understand the issue or your needs, intervalmatch will relate each sales date in the range set between start_date and end_date by emp_id. 

RogerG
Creator
Creator
Author

you are correct, I did a bad job in describing my issue.  

I have a new upload, ignore the other one

 

high level sumary

1 - I have a sales person list with dates, not all dates are important, but they all need to be maintained

2 - I am converting the single list of dates to a start and finish based on the dates in bullet 1, these will make up the sales date ranges, they are grouped by the person, not the entry id in the system.

3 - I have costs are are attached to the entry ID, I needed to find a way to convert that to the sales person not the ID, so I could tie those the ranges.

4 - the costs need to be grouped by the RANGE1 within the sales range 

 

rubenmarin

Hi @RogerG, I have readed it more than 3 times and still don't get it, maybe it will be clearer if you just set the inline tables with your original data and the desired final table.

In case it help ranges can be created as:

EMP_SALES_RANGE:
 LOAD
 	EMP_NAME, 
 	EMP_STATUS,
 	EMP_STATUS_ORDER,
 	TARGET_SALES_DATES AS START_SALES_RANGE,
 	If(EMP_NAME=Peek(EMP_NAME)
 	  ,date(PEEK(START_SALES_RANGE)-1)
 	  ,date(Floor(YearEnd(MakeDate(9999))))
 	) AS END_SALES_RANGE // this date is coming in on my real app
 RESIDENT TEMP 
  WHERE EMP_STATUS_ORDER <> NULL()
 ORDER BY EMP_NAME, EMP_STATUS_ORDER DESC;

Note that i substract one to create end date, this is to avoid the same date in two different ranges (on one as end and on the other as start).

Still I don't know where the date of this line come from: 22, SAM, E, 3/25/2020

RogerG
Creator
Creator
Author

Let me think about how to explain it, even in real terms it confuses me.  I don't want to just throw the problem out there and expect someone to solve it.  hence my attempts at the qvw's  I did make progress and may have to just break it down even more than I did.

sorry for the confusion