topic Re: max date with clm no in QlikView

max date with clm no

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

Good morning community

facing a problem

this are my two filed

Max_date	clm_no
30-Nov-2015	RC-HS15-10325507
02-Dec-2015	RC-HS15-10325827
21-Apr-2016	RR-HC16-10347954
11-Jan-2016	RC-HS15-10332256
13-Jan-2016	RC-HS15-10332457
11-Feb-2016	RC-HS15-10336925
08-May-2016	RC-HS16-10350801
18-Jan-2016	RR-HC15-10333243
08-May-2016	RC-HS16-10350801
08-Apr-2016	RC-HS16-10346076
01-Oct-2015	RR-HC15-10314918
08-May-2016	RR-HC16-10347954

this are my codes used for max date and clm_no

max date :

=MaxString(aggr(MaxString(if(len(KeepChar(CLM_XGEN_CLAIM_NUM,'-'))<>3,CLM_INTIMATION_DATE)),HOSPITAL_CODE,HOSPITAL_NAME,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

and for clm_no is :

=MaxString(aggr(MaxString(if(len(KeepChar(CLM_XGEN_CLAIM_NUM,'-'))<>3,CLM_XGEN_CLAIM_NUM)),HOSPITAL_CODE,HOSPITAL_NAME,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

but its not showing me right output

max date is coming but its not showings clm no

i want when max date comes then its associate clm_no should come

output should be

Max_date	clm_no
30-Nov-2015	RC-HS15-10325507
02-Dec-2015	RC-HS15-10325827
21-Apr-2016	RR-HC16-10347954
11-Jan-2016	RC-HS15-10332256
13-Jan-2016	RC-HS15-10332457
11-Feb-2016	RC-HS15-10336925
08-May-2016	RC-HS16-10350801
18-Jan-2016	RR-HC15-10333243
08-May-2016	RC-HS16-10350801
08-Apr-2016	RC-HS16-10346076
01-Oct-2015	RR-HC15-10314918
08-May-2016	RC-HS16-10350801

can any one help me in this

Re: max date with clm no

Gysbert_Wassenaar — Thu, 21 Jul 2016 05:57:59 GMT

Turn your 'dates' into real dates using the date#() function

LOAD

Date(Date#(Max_date,'DD-MMM-YYYY'),'DD-MMM-YYYY') as Max_date,

clm_no

FROM

....source....

;

Then retrieve the max Max_date value using the max() function: max(Max_date). You can retrieve the clm_no for that max Max_date with the expression FirstSortedValue(clm_no, -Max_date).

Re: max date with clm no

er_mohit — Thu, 21 Jul 2016 05:59:55 GMT

PFA

Re: max date with clm no

sagarrahul — Thu, 21 Jul 2016 06:05:40 GMT

i want only expression

can u give me expression

Re: max date with clm no

sagarrahul — Thu, 21 Jul 2016 06:06:01 GMT

pfa means

Re: max date with clm no

qlikview979 — Thu, 21 Jul 2016 06:22:05 GMT

PFA Means "Please find the attached document or (file)"

Regards

Mahesh

Re: max date with clm no

sagarrahul — Thu, 21 Jul 2016 06:26:03 GMT

its not getting opened its having some problem

Re: max date with clm no

Gysbert_Wassenaar — Thu, 21 Jul 2016 06:30:19 GMT

Ok, if you don't want to learn I have nothing to teach.

Re: max date with clm no

er_mohit — Thu, 21 Jul 2016 06:34:17 GMT

Ok.

Just do copy and paste below mentioned code into script side.

Table:

LOAD Date#(Max_date,'DD-MMM-YYYY') as Max_date,clm_no,SubField(clm_no,'-',-1) as clm_numericno Inline [

Max_date, clm_no

30-Nov-2015, RC-HS15-10325507

02-Dec-2015, RC-HS15-10325827

21-Apr-2016, RR-HC16-10347954

11-Jan-2016, RC-HS15-10332256

13-Jan-2016, RC-HS15-10332457

11-Feb-2016, RC-HS15-10336925

08-May-2016,RC-HS16-10350801

18-Jan-2016,RR-HC15-10333243

08-May-2016,RC-HS16-10350801

08-Apr-2016,RC-HS16-10346076

01-Oct-2015,RR-HC15-10314918

08-May-2016,RR-HC16-10347954

];

Reload it.

After Reload create a straight table chart

Add dimension - max_date

Expression side write FirstSortedValue(clm_no,-aggr(max(clm_numericno),Max_date))

You will get the desired output.

Hope its clear now.

Re: max date with clm no

sagarrahul — Thu, 21 Jul 2016 06:53:22 GMT

mohit ty for ur help but its already there in my table

and just want expression

Re: max date with clm no

sagarrahul — Thu, 21 Jul 2016 06:57:51 GMT

THIS IS MY TABLE