topic Re: Generating the values for missing dates in QlikView

Generating the values for missing dates

ankitbisht01 — Mon, 17 Oct 2016 08:35:59 GMT

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

Re: Generating the values for missing dates

Gysbert_Wassenaar — Mon, 17 Oct 2016 08:58:42 GMT

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;

Re: Generating the values for missing dates

ankitbisht01 — Mon, 17 Oct 2016 11:55:03 GMT

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 .

Re: Generating the values for missing dates

Aurelien_Martinez — Mon, 17 Oct 2016 12:59:54 GMT

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