topic Re: interval match based on multiple fields and 2 resident tables in QlikView

interval match based on multiple fields and 2 resident tables

RogerG — Fri, 08 Jan 2021 20:35:35 GMT

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_ID	SALES_DATE	RANGE1
1	1/15/2020	E1R1
1	2/11/2020	E1R1
1	3/30/2020	E1R2
2	1/11/2020	E2R1
2	2/11/2020	E2R1
2	3/25/2020	E2R2
3	1/1/2020	E3R1
3	2/11/2020	E3R1
3	3/27/2020	E3R2

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

EMP_ID	SALES_DATE	RANGE1	START_DATE	END_DATE
2	1/11/2020	E2R1	1/3/2020	2/15/2020
2	1/11/2020	E2R2	3/1/2020	4/15/2020
1	2/11/2020	E1R1	1/16/2020	2/28/2020
2	2/11/2020	E2R1	1/3/2020	2/15/2020
2	2/11/2020	E2R2	3/1/2020	4/15/2020
3	2/11/2020	E3R2	3/1/2020	3/15/2020

Re: interval match based on multiple fields and 2 resident tables

rubenmarin — Fri, 08 Jan 2021 19:08:52 GMT

Hi @RogerG, whit that code i get this:

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.

Re: interval match based on multiple fields and 2 resident tables

RogerG — Fri, 08 Jan 2021 20:31:53 GMT

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

Re: interval match based on multiple fields and 2 resident tables

rubenmarin — Sat, 09 Jan 2021 10:02:43 GMT

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

Re: interval match based on multiple fields and 2 resident tables

RogerG — Sat, 09 Jan 2021 15:30:42 GMT

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