What the best way to complete dates and parameters in daily table ?

[Xabinav]

Hi, I trying to create Table no'2  from this form -   

* Date form DD/MM/YYY

appreciate your help please.

Basic Table 

Date	Parameter
Today()	-
20/08/2023	6.5
18/08/2023	4

Destination table

Date	Parameter
22/08/2023  (assume this is today date...)	6.5
21/08/2023	6.5
20/08/2023	6.5
19/08/2023	4
18/08/2023	4

[Oleg_Troyansky]

Hi there,

There might be a more elegant solution to this, but the straight-forward solution that comes to my mind at the moment is a multi-step process:

1. Find the lowest date in your input table - that will be your starting point.

2. Generate daily records for every day since the lowest date until today's date, using the Autonumber load.

3. Left-Join your input table to the generated table, using the Date field as the join key. This will populate your Parameter field for those dates that exist in the input table.

4. Reload the table created in step 3, sorted by Date, and every time the field Parameter is empty, replace the NULL value with the value from the previous row, for example like this:

LOAD

...

Alt(Parameter, Peek('Parameter'))   as Parameter

...

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!