topic find first dat from table after or equal to date other table (Qlikview) in App Development

find first dat from table after or equal to date other table (Qlikview)

curiousfellow — Thu, 07 Dec 2023 15:14:09 GMT

I have a table (1) containing a date field and I have a table(2) with weeknumbers (not calenderweeks) and their lastdate.

In my load script I want to find the first date equal or after the date in table(1) and add the weeknumber from table(2) to table(1)

Re: find first dat from table after or equal to date other table (Qlikview)

Vegar — Thu, 07 Dec 2023 19:40:23 GMT

I am guessing a bit here, but what if you try to use interval match. See sample script below.

///Your table (1) containing dates Table_1: LOAD dayname(makedate(2023,11,12)+ recno()) as Date AutoGenerate 28; //Your table (2) containing weeknumber and an end date for that week Table_2: LOAD * Inline [ Week, WeekEndDate W49, 2023-12-10 W48, 2023-12-03 W47, 2023-11-26 W46, 2023-11-19 ]; //Intervalmatching dates based on weekstartdate (calculated) and weekenddate IntervalMatch(Date) LOAD WeekStart(WeekEndDate) as WeekStartDate, WeekEndDate Resident Table_2;

Re: find first dat from table after or equal to date other table (Qlikview)

curiousfellow — Mon, 11 Dec 2023 09:59:18 GMT

I must admit that I find the interval function hard to understand. Besides that I am not sure it can be used in my situation. What I did not mention is that the weeknumbers cover a period of two years and are overlapping.
Please have a look at my tables and the result I am looking for.

table1_my_data:

Load * Inline[
Production_year,Date1,date2,other_value,other_value2
2023,30-11-2023,09-12-2023,text2,100
2023,05-01-2024,23-02-2024,text,200
2023,16-02-2024,01-03-2024,othertext,14
2023,03-12-2023,12-12-2023,text13,18
2023,10-12-2024,11-12-2023,text9,19
2024,18-02-2024,25-02-2024,othertext,300
];

table2_weeknumer_table:
Load *Inline

[Production_year,weeknumber,date

2023,63,10-12-2023
2023,64,17-12-2023
2023,65,24-12-2023
2023,66,31-12-2023
2023,67,07-01-2024
2023,68,14-01-2024
2023,69,21-01-2024
2023,70,28-01-2024
2023,71,04-02-2024
2023,72,11-02-2024
2023,73,18-02-2024
2023,74,25-02-2024
2023,75,03-03-2024
2024,58,04-11-2024
2024,59,11-11-2024
2024,6,05-11-2023
2024,7,12-11-2023
2024,8,19-11-2023
2024,9,26-11-2023
2024,10,03-12-2023
2024,11,10-12-2023
2024,12,17-12-2023
2024,13,24-12-2023
2024,14,31-12-2023
2024,15,07-01-2024
2024,16,14-01-2024
2024,17,21-01-2024
2024,18,28-01-2024
2024,19,04-02-2024
2024,20,11-02-2024
2024,21,18-02-2024
2024,22,25-02-2024
2024,23,03-03-2024
2024,24,10-03-2024
2024,25,17-03-2024
2024,26,24-03-2024];

expected result :

Production_year	Date1	date2	other_value	other_value2	weeknumber_date1	weeknumber_date2
2023	30-11-2023	09-12-2023	text2	100	62	63
2023	05-01-2024	23-02-2024	text	200	67	74
2023	16-02-2024	01-03-2024	othertext	14	73	75
2024	03-12-2023	12-12-2023	othertext	300	10	12
2024	10-12-2024	11-12-2023	text13	10	11	12
2024	18-02-2024	25-02-2024	text9	25	21	22