Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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;
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 |