topic Re: how to get continuous date in QlikView

how to get continuous date

Tue, 12 Mar 2013 12:52:42 GMT

i tried a lot but can't get success

plzz help me through out of it

my sample table is this

date	Absent
1/1/2013	A
1/2/2013	A
1/5/2013	A
1/7/2013	A
1/8/2013	A
1/9/2013	A
1/12/2013	A
1/13/2013	A
1/18/2013	A

i have input box with variable

when

INPUTBOX_---		OUTPUT
variable	2	date	Absent
		1/1/2013	A
		1/2/2013	A
		1/7/2013	A
		1/8/2013	A
		1/9/2013	A
		1/12/2013	A
		1/13/2013	A

when

INPUTBOX_---
variable	3

OUTPUT
date	Absent
1/7/2013	A
1/8/2013	A
1/9/2013	A

plzzzzz help me your help will be appreciated.

thnx in advance

Re: how to get continuous date

Tue, 12 Mar 2013 13:17:03 GMT

plz helppppp me

Re: how to get continuous date

Gysbert_Wassenaar — Tue, 12 Mar 2013 13:29:15 GMT

See attached qvw.

Re: how to get continuous date

Tue, 12 Mar 2013 14:09:06 GMT

hiiiii Gysbert Wassenaar

thnx for reply. m using personal edition so, can't open your application

plzz explain briefly

Re: how to get continuous date

Gysbert_Wassenaar — Tue, 12 Mar 2013 17:42:52 GMT

This is the script:

// load the data and create a field ConsecFlag. If the date is one day

// later than the previous date then ConsecFlag is 1 + the value of

// ConsecFlag of the previous record. Otherwise it is 1.

T1:

load *, if(previous(date)=date-1,rangesum(1, peek(ConsecFlag)),1) as ConsecFlag;

LOAD date#(date,'MM/DD/YYYY') as date, Absent INLINE [

date, Absent

1/1/2013, A

1/2/2013, A

1/5/2013, A

1/7/2013, A

1/8/2013, A

1/9/2013, A

1/12/2013, A

1/13/2013, A

1/18/2013, A

];

// Order the table by date desc and check if the value of ConsecFlag

// is smaller than the value of ConsecFlag of the previous record. If it

// isn't then use ConsecFlag. If it isn't use the value stored in the

// previous record of the target table T2.

T2:

NoConcatenate

load date, Absent,

if(ConsecFlag<previous(ConsecFlag),peek(ConsecFlag),ConsecFlag) as ConsecFlag

Resident T1 order by Absent, date desc;

drop table T1;

In the UI I added an inputbox that changes the value of a variable named vLength. There's also a straight table with date and Absent as dimensions and as expression sum({<ConsecFlag={'>=$(vLength)'}>}1). This expression is hidden so it doesn't show in the table.