Find corresponding minimum value

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Need help as after two days of efforts I am lost.

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.

I hope you can help. Thanks in advance.

topic Find corresponding minimum value in QlikView

Find corresponding minimum value