max date with clm no

[sagarrahul]

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

[Gysbert_Wassenaar]

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).

[er_mohit]

Hi

PFA

[sagarrahul]

i want only expression

can u give me expression

[sagarrahul]

pfa means

[qlikview979]

Hi

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

Regards

Mahesh

[sagarrahul]

its not getting opened its having some problem

[Gysbert_Wassenaar]

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

[er_mohit]

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.

[sagarrahul]

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

and just want expression