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
Can you give an example output you are looking based on your sample attached?
Hi Vishwarth,
Thanks for the quick reply.
Please refer the highlighted rows in yellow.
Those are the missing dates in the source and I want the data to be copied from 9/26 till 9/29 from the source data of 9/25.
Again this should be dynamic logic for all such missing data.
Regards
Jeba
Can someone guide me with the right path?
Hi,
I would approach it in two steps (in the script):
1. join all dates (your field value is PERIOD) to the fact table which has the ID and Type fields
2. use Peek() function to fetch the value of the previous record for the Limit and Required; in this Resident load you need not to forget the Order By statement - in your case it will be something like Order By ID, Type, PERIOD asc
Hope this helps,
Radovan
This is what I am trying,but didn't work
LOAD ID,
PERIOD,
Type,
Limit,
Required
//ID&PERIOD&Type&Limit&Required AS KEY
FROM
[Sample.xlsx]
(ooxml, embedded labels, table is Sample);
NOCONCATENATE
TABLE:
LOAD
*,
DATE (FLOOR (PERIOD), 'YYYYMMDD') AS KEY_PERIOD,
DATE (FLOOR (PERIOD), 'YYYYMMDD') AS EFFECTIVE_DATE
RESIDENT INLINE;
DROP TABLE INLINE;
MinMax: // make min and max for every ID
load
ID,
//KEY,
Type,
Limit,
Required,
min(PERIOD) as MinPeriod,
today()-1 as MaxPeriod
Resident TABLE
group by ID,Type,Limit,Required;
TABLE2: // all required date between min and max (for every ID)
Load
ID,
// Type,
// Limit,
// Required,
date(MinPeriod -1 + IterNo()) as PERIOD
Resident MinMax
While IterNo() <= MaxPeriod - MinPeriod +1;
DROP Table MinMax;
Left join (TABLE2) // join with source table (in case you have to add some field from source)
load *
//if(len(trim([Type]))=0,Previous([Type]),[Type]) as [Type1]
resident TABLE;
DROP Table TABLE;
RENAME table TABLE2 to TABLE;
NoConcatenate
FINAL:
Load
PERIOD,
ID,
If(LEN(TRIM(Type))=0 AND PERIOD = Peek(PERIOD),Peek(Type),Type) as Type,
If(LEN(TRIM(Limit))=0,Peek(Limit),Limit) as Limit,
If(LEN(TRIM(Required))=0,Peek(Required),Required) as Required
//and Limit= peek(Limit) and Limit= peek(Limit), Peek(Warehouse), Warehouse) as Warehouse
Resident TABLE Order by PERIOD,ID;
DROP TABLE TABLE;
Did you had a chance to review the sample output?
Sorry Dass, was super busy with some stuff so could not spend time. will check tomorrow if thats ok?
maybe helpful:
Hi,
Please try this,
If(isnull(date),previous(date),date)
Hope this helps