Generating the values for missing dates

[ankitbisht01]

Hi all,

I have one challenge with my data set, that is:

-------> I have data regarding  Account numbers , in form of status = 0 or 2.

-------> That data is present only of some dates on which these above status were given or updated.

-------> All i want to generate the current  status for each date of calendar regarding a particular Account Number till the status changes ,

          After change in status, this status will be generated for further dates, till the status changes again.

example : This example is for a particular account number  and i have several account number in my data.

Account Num	Date	Status
C00001	3/31/2010	0
C00001	4/20/2015	0
C00001	4/25/2015	2

data is like this for each Account Number , i want to have status "0" from date 3/31/2010 - 4/20/2015 for each date comes in between and Status "2" for date 4/20/2015 - 4/25/2015.

I am using master calendar to generate all the dates.

Attaching the data set.

Best Regards

Ankit

[Gysbert_Wassenaar]

Something like this:

Temp1:

LOAD [Account Num],

     Date,

     Status

FROM

     [data.xls]

     (biff, embedded labels, table is Sheet1$)

     ;

Result:

NOCONCATENATE

LOAD

     [Account Num],

     Date(Date + IterNo() -1,'M/D/YYYY') as Date,

     Status

WHILE

     Date(Date + IterNo() -1) <= PrevDate

     ;

LOAD

     [Account Num],

     Date,

     Date(If(Previous([Account Num]) = [Account Num] and Previous(Status)=Status,Previous(Date)-1),'M/D/YYYY') as PrevDate,

     Status

RESIDENT

     Temp1

ORDER BY

     [Account Num],

     Status,

     Date desc

     ;

DROP TABLE Temp1;

[ankitbisht01]

Thank you for such a quick response Gysbert,  but script doesn't seems to be working .

Not able to get all the dates with right status .

[Aurelien_Martinez]

Hi,

//données de base
Flux_A:
LOAD *
Inline [
Account Num, Date, Status
C00001, 31/03/2010, 0
C00001, 20/04/2015, 0
C00001, 25/04/2015, 2
]
;




//récup min max pour le calendrier
_TMP:
LOAD
  Min(Date) as min,
  max(Date) as max
Resident Flux_A
;


LET vmin = Peek('min');
LET vmax = Peek('max');


//calendrier
CAL:
LOAD
  Date($(vmin) + RecNo() - 1) as _Date
AutoGenerate($(vmax) - $(vmin)) +1 
;


//interval
Join(Flux_A)
IntervalMatch(_Date)
LOAD
  Alt(Previous(Date), Date) as Date_déb,
  Date as Date_fin
Resident Flux_A
;


DROP Tables _TMP, CAL;

Aurélien

Consultant Excelcio