Repeat dates up to today

[reporting_neu]

Hello,
I have the following data:

AnNo	StampDate	Time
153	28.09.2024	10
153	29.09.2024	11
153	30.09.2024	13
845	28.09.2024	35
845	29.09.2024	32
845	30.09.2024	38

If the StampDate is not today, Qlik should continue to keep the date until Qlik reaches today's date. The value should remain the same until then.

Result (today is 01.10.2024):

AnNo	StampDate	Time
153	28.09.2024	10
153	29.09.2024	11
153	30.09.2024	13
153	01.10.2024	13
845	28.09.2024	35
845	29.09.2024	32
845	30.09.2024	38
845	01.10.2024	38

I tried with this script. But then it loads indefinitely.

Temp:
Load
 AnNo
,Time
,Date(StampDate+ IterNo()-1) as StampDate
Resident X
WHILE floor(StampDate+ IterNo()-1) < Date(Today());

How can I implement this in the script?

[VBD]

Hello,

Maybe you have a problem with your dates format

I tried something like this and it seems to works :

TABLE:
LOAD
AnNo,
Date(Date#(StampDate,'dd.MM.YYYY')) as StampDate,
"Time"
FROM [lib://DataFiles/test.xlsx]
(ooxml, embedded labels, table is Feuil1);

Temp:
NoConcatenate
Load
AnNo
,MaxTimeByAnNo
,Time
, Date(StampDate+ IterNo()-1) as StampDate
Resident TABLE
WHILE floor(StampDate+ IterNo()-1) <= Date(Today());

Drop table TABLE;

Regards

[reporting_neu]

Thanks for your answer. Unfortunately there is too much data, which is why loading with this script takes too long. 😞

But I have a new solution. If there are gaps, the date is entered here, but the previous values ​​are not adopted:

NoConcatenate
TMP1:
Load
AnNo
,Time
,StampDate
Resident X;

Drop Table X;

MinMaxDate:
Load
AnNo
,Min(StampDate) as MinStampDate
,Today() as MaxStampDate
Resident TMP1
Group By AnNo;

Join (TMP1)

Load
AnNo
,Date(iterno()+ MinStampDate) as StampDate
Resident MinMaxDate
While iterno()+ MinStampDate <= MaxStampDate;

TMP2:
NoConcatenate
Load
AnNo
,StampDate
,If(IsNull(Time) and AnNo = Peek(AnNo), Peek(Time), Time) as Time
Resident TMP1
Order By
AnNo
,StampDate
;

Drop Tables MinMaxDate, TMP1;

Result:

[Kushal_Chawda]

@reporting_neu  

Data:
Load * Inline [
AnNo	StampDate	Time
153	28.09.2024	10
153	29.09.2024	11
153	30.09.2024	13
845	28.09.2024	35
845	29.09.2024	32
845	30.09.2024	38] (delimiter is '\t');

Join(Data)
Load AnNo,
     date(MinDate+IterNo()-1) as StampDate
While MinDate+IterNo()-1 <= Today();
Load AnNo,
     date(min(StampDate)) as MinDate
Resident Data
Group by AnNo;

// Fill Time Field values for missing dates. You might need to fill other field values
Final:
NoConcatenate
Load *,
     if(IsNull(Time),Peek('Time1'),Time) as Time1
Resident Data
Order by AnNo,StampDate;

Drop Table Data;

// Dropping original Field and Rename New field to original
Drop Field Time;Rename Field Time1 to Time;

[reporting_neu]

Thanks for your answer. Unfortunately I have the same result again, that the values ​​are not transferred to the new field.

Something needs to be adjusted here:

if(IsNull(Time),Peek('Time1'),Time) as Time1

[Kushal_Chawda]

@reporting_neu  It should work unless you data is still not ordered properly. Just highlighting below that Field name in peek() should be same as field name. In my logic I am creating Time field with new name. I am then dropping original one and renaming new one back to original.

if(IsNull(Time),Peek('Time1'),Time) as Time1

[reporting_neu]

Hmm... I sorted the table beforehand.

Order by
AnNo
,StampDate
,Time
;

 I then adopted your script in exactly the same way, created a new field, deleted the old field and renamed the new field so that it had the old name.

[Kushal_Chawda]

@reporting_neu  Could you post the screenshot of your script?

[reporting_neu]

// 3.0 Originial
//********************************************************************************************************

Originial:
LOAD
AnNo
,Date(Floor(StampDate)) as StampDate
,Time_field
;
[Originial]:
SELECT
AnNo
,StampDate
,Time_field
FROM dbo."Originial"
Where
TimeID = 115;


// 3.1 
//********************************************************************************************************

NoConcatenate
TMP1:
Load
AnNo
,StampDate
,Interval(Time_field/24/60, 'hh:mm') as Flextime
Resident Originial
Where StampDate >= '01.01.2021';

Drop Table Originial;


// 3.2 
//********************************************************************************************************

NoConcatenate
TMP2:
Load
AnNo
,Flextime
,StampDate 
Resident TMP1;

Drop Table TMP1;

Join(TMP2)
Load
AnNo
,Date(MinStampDate+IterNo()-1) as StampDate
While MinStampDate+IterNo()-1 <= Today();
Load
AnNo
,Date(min(StampDate)) as MinStampDate
Resident TMP2
Group by
AnNo;

NoConcatenate
TMP3:
Load
AnNo
,StampDate
,Flextime
Resident TMP2
Order by
AnNo
,StampDate
,Flextime
;

Drop Table TMP2;

// Fill Time Field values for missing dates. You might need to fill other field values

Qualify '*';
Unqualify '%*';

Account:
NoConcatenate
Load
AnNo
,StampDate
,Flextime
,if(IsNull(Flextime),Peek('Flextime1'),Flextime) as Flextime1
Resident TMP3
Order by
AnNo
,StampDate;

Unqualify '*';

Drop Table TMP3;

// Dropping original Field and Rename New field to original
Drop Field Account.Flextime; Rename Field Account.Flextime1 to Account.Flextime;

[reporting_neu · Accepted solution]

I found the solution 😄

Account:
NoConcatenate
Load
AnNo
,StampDate
,Flextime
,if(Previous(AnNo) = AnNo and len(Flextime)<1,peek('Flextime1'),Flextime) as Flextime1
Resident TMP3
Order by
AnNo
,StampDate
;

The first thing I thought was that Qlik doesn't recognize null. But that's not the point. I have no idea what's different now. Previous(AnNo) I had already tested it again and again. But without success. Luckily now it works!