I have one master table and one history table where I need corresponding minimum values from history table. Let me explain in terms of data
Master table
ID
Date
JulianDate
101
01/May/17
3000
102
07/May/17
4000
History table
ID
Date
JulianDate
Value
101
01/May/17
3000
1
101
02/May/17
3001
2
101
03/May/17
3002
3
102
07/May/17
4000
1
102
08/May/17
4001
4
Issue is I need data for 7 days where I have data for 3 days for ID 101 and 2 days for 102. Business logic is to capture the closest past date's "Value" from History table where dates are missing in 7 days. In case of ID 101, missing dates are 04,05,06 May and for these dates 03 May's "Value" should be used. In case of 102, for 06 May "Value" of 07 May should be used and for 09, 10, 11, 12, 13 May "Value" of 08 May should be used.
To achieve this I connected two tables with left join and created 7 Julian days columns (D1 -> D7) by adding 1 incrementally.
ID
Date
JulianDate1
JulianDate2
JulianDate3
JulianDate4
JulianDate5
JulianDate6
JulianDate7
101
01/May/17
3000
3001
3002
3003
3004
3005
3006
102
07/May/17
4000
4001
4002
4003
4004
4005
4006
Now I subtracted Julian date of History from Master table to find difference in days. I created 7 difference columns carrying results of subtractions.
After this I ran Load Min(Diff1), ID, Value resident Master Group by ID, Value. This query goes running for 40 minutes before I aborted it. I am sure there will be a better way to do this in Qlik Script not UI.
