topic Re: date difference in QlikView

date difference

sagarrahul — Wed, 25 Nov 2020 16:16:04 GMT

	in i want
	current date subtract max date ie (current date - max date )
	after that when it output arrives if the differnce between date is 1 day ie
	eg (04-Feb-2016 - 03-Feb-2016) then i another column it should show difference as '1'and in another column its sholud its claim no difference
	and its max date in another coulmn .

CLM_INTIMATION_DATE	max date	claim no	current date
28-Jan-2016	28-Jan-2016	RC-HS15-10335711	5/30/2016
03-Feb-2016	03-Feb-2016	RC-HS15-10341139	5/30/2016
08-Mar-2016	08-Mar-2016	RC-HS15-10343361	5/30/2016
22-Mar-2016	22-Mar-2016	RC-HS15-10336529	5/30/2016
08-Feb-2016	08-Feb-2016	RC-HS15-10336520	5/30/2016
08-Feb-2016	08-Feb-2016	RC-HS15-10337526	5/30/2016
15-Feb-2016	22-Mar-2016	RC-HS15-10343361	5/30/2016

11-Feb-2016	12-Feb-2016	RR-HS15-10336940	5/30/2016
11-Feb-2016	13-Feb-2016	RR-HS15-10336940	5/30/2016
	14-Feb-2016	RR-HS15-10341978	5/30/2016

20-Jan-2016	20-Jan-2016	RR-HS15-10341246	5/30/2016
09-Mar-2016	09-Mar-2016	RR-HS15-10341246	5/30/2016
09-Mar-2016	07-Mar-2016	RR-HS15-10341246	5/30/2016

so pls help me in this as soon as possible

Re: date difference

mightyqlikers — Tue, 31 May 2016 05:02:32 GMT

could you please share expected out put and input in excel..

$@M.

Re: date difference

PrashantSangle — Tue, 31 May 2016 05:05:41 GMT

Hi,

try,

for date difference

If(interval(currentDate - maxDate,'D')>0,interval(currentDate - maxDate,'D'))

for Claim No

If(interval(currentDate - maxDate,'D')>0,ClaimNo)

If your date contain timestamp also then use floor() to round the date value

Regards

Re: date difference

sagarrahul — Tue, 31 May 2016 05:57:36 GMT

thank u max dreamer for ur time and help to me .

u gave its work nicely but my work is half done

now i wnt this

current date - max date that is ( 31-May-2016 - 30-May-2016 ) so its comes 1 and difference is also 1

when difference comes to 1 it must show it max date and along with claim no

Re: date difference

PrashantSangle — Tue, 31 May 2016 06:04:04 GMT

Where ?? in same column??

then

If(interval(currentDate - maxDate,'D')>0,

interval(currentDate - maxDate,'D')=1,

maxDate & ' - ' & ClaimNo,interval(currentDate - maxDate,'D')))

Note : might be I am missed some bracket or semicolon. Please correct it

But logic to achieve your requirement is identify where date difference is 1 if it is equal to 1 then

concatenate two value using & &

Regards

Re: date difference

sagarrahul — Tue, 31 May 2016 06:56:37 GMT

no they are in different different column so now wht

and its not working properly

Re: date difference

Kushal_Chawda — Tue, 31 May 2016 07:03:25 GMT

What if difference is 0 or >1?

Re: date difference

sagarrahul — Tue, 31 May 2016 09:02:43 GMT

dif wil b one day day or more

Re: date difference

PrashantSangle — Tue, 31 May 2016 09:05:39 GMT

Hi,

can tell us what is your expected output in provided sample data.??

So it will be easy to us to provide solution

Regards

Re: date difference

sagarrahul — Tue, 31 May 2016 10:01:40 GMT

k will provide u

Re: date difference

sagarrahul — Tue, 31 May 2016 10:16:20 GMT

i max dreamer this is whr i want in my output .


max date	claim no	current date	differnce (current date-max date)	clm num
28-Jan-2016	RC-HS15-10335711	5/30/2016	125	here dif is not 1 so it should be blank
29-May-2016	RC-HS15-10341139	5/30/2016	1	RC-HS15-10341139
08-Mar-2016	RC-HS15-10343361	5/30/2016	119	-
22-Mar-2016	RC-HS15-10336529	5/30/2016	85	-
08-Feb-2016	RC-HS15-10336520	5/30/2016	85	-
08-Feb-2016	RC-HS15-10337526	5/30/2016	71	-
29-May-2016	RC-HS15-10343361	5/30/2016	1	RC-HS15-10343361
12-Feb-2016	RR-HS15-10336940	5/30/2016	114
13-Feb-2016	RR-HS15-10336940	5/30/2016	114
14-Feb-2016	RR-HS15-10341978	5/30/2016	107
29-May-2016	RR-HS15-10341246	5/30/2016	1	RR-HS15-10341246
09-Mar-2016	RR-HS15-10341246	5/30/2016	71
07-Mar-2016	RR-HS15-10341246	5/30/2016	111

Re: date difference

PrashantSangle — Tue, 31 May 2016 10:26:03 GMT

Hi,

For diff

interval(currentDate-maxDate)

for clum Num

if(interval(currentDate-maxDate)=1,claimNo)

Regards

Re: date difference

Peter_Cammaert — Tue, 31 May 2016 10:31:31 GMT

Use this for column clm num:

IF (Interval(currentDate - maxDate, 'D') = 1, ClaimNo)

or to make your table independent from the currentDate column, use

IF (Interval(today() - maxDate, 'D') = 1, ClaimNo)

Re: date difference

sagarrahul — Tue, 31 May 2016 10:36:34 GMT

ty bro it working fine

Re: date difference

sagarrahul — Tue, 31 May 2016 10:36:43 GMT

ty bro it working fine

Re: date difference

PrashantSangle — Tue, 31 May 2016 11:26:13 GMT

Hi,

If you got the correct answer then please close the thread.

Regards