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;
