Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
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
Regards
Jeba
Hi Marco,
Unfortunately this is not working in my case
No this doesn't work.
I already tested multiple options
Hi Marco,
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
Any thoughts?
Regards
Jeba
Sure. no issues
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 )
Sample:
noconcatenate load
ID &''& num(PERIOD) &''& Type as _key_exists,
ID,
PERIOD,
Type,
Limit,
Required
from
[..\Downloads\Sample.xlsx]
(ooxml, embedded labels, table is Sample);
t_missing_records:
noconcatenate load
ID, Type, min(PERIOD) as PERIOD_start
resident
Sample
group by
ID, Type;
join_all_dates:
join (t_missing_records) load
t_min_date + iterno() as PERIOD
while
t_min_date + iterno() <= t_max_date;
noconcatenate load
min( fieldvalue( 'PERIOD', recno() ) ) - 1 as t_min_date,
max( fieldvalue( 'PERIOD', recno() ) ) as t_max_date
autogenerate
fieldvaluecount( 'PERIOD' );
concatenate (Sample) load
ID,
PERIOD,
Type
resident
t_missing_records
where
not exists( _key_exists, ID &''& num(PERIOD) &''& Type )
and num(PERIOD) >= PERIOD_start;
drop table t_missing_records;
Final:
noconcatenate load
ID,
PERIOD,
Type,
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
resident
Sample
order by
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.
Radovan
Hi Radovan
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:
Final:
noconcatenate load
ID,
PERIOD,
Type,
Limit,
Required,
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
resident
Sample
order by
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?
Radovan
Hi Radovan,
This should work,
Let me play around and confirm so everyone has the right answer
Regards
Jeba