Add missing data with values ​​from the previous day

[reporting_neu]

Hello,

I have the following data structure:

Part	Datefield1	hKP2	...	...
100	15.12.2024	10	...	...
100	17.12.2024	10	...	...
100	18.12.2024	11	...	...
100	19.12.2024	12	...	...
133	15.12.2024	4442	...	...
133	16.12.2024	4532	...	...
133	17.12.2024	4433	...	...
133	18.12.2024	4444	...	...

As you can see, the entry from December 18, 2024 is missing here. Overall, many days are missing.
I would like the missing days to be supplemented with the values ​​from the previous day and the missing date to be entered.

The result should look like this:

Part	Datefield1	hKP2	...	...
100	15.12.2024	10	...	...
100	16.12.2024	10	...	...
100	17.12.2024	12	...	...
100	18.12.2024	11	...	...
100	19.12.2024	12	...	...
133	15.12.2024	4442	...	...
133	16.12.2024	4532	...	...
133	17.12.2024	4433	...	...
133	18.12.2024	4444	...	...

Can you tell me how I can do that?

[Clement15]

Hello,

Does this meet your needs?

TEST:
load *
Inline [
Part,Datefield1,hKP2
100,15.12.2024,10
100,17.12.2024,10
100,18.12.2024,11
100,19.12.2024,12
133,15.12.2024,4442
133,16.12.2024,4532
133,17.12.2024,4433
133,18.12.2024,4444
];

NoConcatenate

TEST2:
load
If(RowNo()=1,Part,if(previous(Datefield1)>Datefield1+1,Part,Previous(Part))) as Part,
Date(If(RowNo()=1,Datefield1,if(previous(Datefield1)>Datefield1+1,Datefield1,Previous(Datefield1)+1))) as Datefield1,
If(RowNo()=1,hKP2,if(previous(Datefield1)>Datefield1+1,hKP2,Previous(hKP2))) as hKP2;
load 
Part,
Date(Date#(Datefield1,'DD.MM.YY')) as Datefield1,
hKP2
Resident TEST
order by Part;

Drop table TEST;

[Kushal_Chawda]

@reporting_neu  try below

Data:
load *
Inline [
Part,Datefield1,hKP2
100,15.12.2024,10
100,17.12.2024,10
100,18.12.2024,11
100,19.12.2024,12
133,15.12.2024,4442
133,16.12.2024,4532
133,17.12.2024,4433
133,18.12.2024,4444
];

join(Data)
Load Part,
     date(min_date+IterNo()-1) as Datefield1
While min_date+IterNo()-1<=max_date;
Load Part,
    min(Datefield1) as min_date,
    max(Datefield1) as max_date
Resident Data
Group by Part;

Final:
NoConcatenate
Load Part,
     Datefield1,
     if(Previous(Part)=Part and len(trim(hKP2))=0,Peek(hKP2),hKP2) as hKP2
Resident Data
Order by Part,Datefield1;

Drop Table Data;
   

[reporting_neu]

I always get the errorcode16 after some loading time 😞

[Kushal_Chawda]

Refer to the below link for this error.

https://community.qlik.com/t5/App-Development/ErrorCode-16-translates-into-quot-Connection-lost-Make...

[Kushal_Chawda]

@reporting_neu make sure that you are joining data on correct field. I am performing join on Part and Date field so both field name should match in both table. With large data set wrong join may cause issue and even group by. try below method and see if it works.

Data:
load *
Inline [
Part,Datefield1,hKP2
100,15.12.2024,10
100,17.12.2024,10
100,18.12.2024,11
100,19.12.2024,12
133,15.12.2024,4442
133,16.12.2024,4532
133,17.12.2024,4433
133,18.12.2024,4444
];

sort:
Load distinct Part,
         Datefield1
Resident Data
Order by Part;

group:
Load Part,
         min(Datefield1) as Min_Date,
         max(Datefield1) as Max_Date
Resident sort
Group by Part;

Drop Table sort;

Join(Data)
Load Part,
        date(Min_Date+IterNo()-1) as Datefield1
Resident group
while Min_Date+IterNo()-1<=Max_Date;

Drop Table group;

Final:
NoConcatenate
Load Part,
         Datefield1,
         if(Previous(Part)=Part and len(trim(hKP2))=0,Peek(hKP2),hKP2) as hKP2
Resident Data
Order by Part,Datefield1;

Drop Table Data;

[Qrishna]

When  working with Groups, its better to use Autonumbered composite keys, also for finding min max try converting the date into nums for better performances. try below: 

Data:
LOAD Part,
Datefield1,
hKP2,
Floor(Date#(Datefield1, 'dd.mm.yyyy')) AS DateNum,
AutoNumber(Part & Floor(Date#(Datefield1, 'dd.mm.yyyy'))) as %Key
FROM
[https://community.qlik.com/t5/New-to-Qlik-Analytics/Add-missing-data-with-values-from-the-previous-d...]
(html, codepage is 1252, embedded labels, table is @1);

// Calculate MinDate and MaxDate for each Part
MinMax:
LOAD Distinct
AutoNumber(Part & MinDate + IterNo() - 1) as %Key,
Part,
MinDate + IterNo() - 1 as DtNum,
Date(MinDate + IterNo() - 1) AS GeneratedDate
WHILE MinDate + IterNo() - 1 <= MaxDate;
LOAD Part,
Min(DateNum) AS MinDate,
Max(DateNum) AS MaxDate
RESIDENT Data
GROUP BY Part;

left join(MinMax)
Load %Key, hKP2
resident Data;
drop table Data;

OutputTable:
Load Part,
GeneratedDate as Datefield1,
if(Previous(Part)=Part and len(trim(hKP2))=0, Peek(hKP2), hKP2) as hKP2
Resident MinMax
order by Part, DtNum;
drop table MinMax;