Could you please help me with the problem I am facing?
I would like to fill the missing dates and it values from the previous day's values.
I tried multiple logic provided in the community,unfortunately I didn't get the required output.
This may be because of the data I have.
Here are the challenges.
ID will have multiple rows per day
If there is no data for a particular day,then I want all the rows from previous day's data to be populated and have a new date as missing date and this needs to created until today
Attaching the sample data which will help more
My data has multiple rows per day and I want all the rows to be copied to the next day if it is missing.
Unfortunately this works only for the last row and previous rows were skipped
Hi, this should work as you need. (I wrote it quickly so sorry if there are mistakes but I believe you can get the point )
ID &''& num(PERIOD) &''& Type as _key_exists,
(ooxml, embedded labels, table is Sample);
ID, Type, min(PERIOD) as PERIOD_start
join (t_missing_records) load
t_min_date + iterno() as PERIOD
t_min_date + iterno() <= t_max_date;
min( fieldvalue( 'PERIOD', recno() ) ) - 1 as t_min_date,
max( fieldvalue( 'PERIOD', recno() ) ) as t_max_date
fieldvaluecount( 'PERIOD' );
concatenate (Sample) load
not exists( _key_exists, ID &''& num(PERIOD) &''& Type )
and num(PERIOD) >= PERIOD_start;
drop table t_missing_records;
if( ID = peek(ID) and Type = peek(Type), peek(Limit), Limit ) as Limit,
if( ID = peek(ID) and Type = peek(Type), peek(Required), Required ) as Required
ID, Type, PERIOD asc;
drop table Sample;
I didn't review your code but the problem might have been in the last IF statements with the peek().
Hope it will help.
Thanks for the guidance.
The code works perfectly fine with the data sample.
However this doesn't work when we add the new data.
Please refer the highlighted rows and the output in the right side.
This code create multiple records based on the _key_exists.
The final output should be exactly as of previous day irrespective of the data changes.
Is that possible?
Ahh, yeah you're right, I missed that.
Well a quick fix would be to change the last resident load like this:
if( isnull(Limit) and ID = peek(ID) and Type = peek(Type), peek(Limit_new), Limit ) as Limit_new,
if( isnull(Required) and ID = peek(ID) and Type = peek(Type), peek(Required_new), Required ) as Required_new
ID, Type, PERIOD asc;
So the "_new" fields will now replicate the previous number if there is NULL, otherwise they will include the source number.
Would this do what you need?