same date occurring for two time but can tress another value

[sagarrahul]

POL_BDM_NAME	POL_BDM_CODE	HOSPITAL_NAME	HOSPITAL_CODE	CLM_LEVEL_OF_CARE	ICD_CODE_LEVEL1_org	Claim_Type	ICD Code Blacklist	total count of claims	Value	status	Max_date	clm_no	Current_date	Differnce	clm_num
ASHISH HALLAN	100169	AASTHA LOK HOSPITAL PVT LTD	HEGIC-HS-21040	Primary	I10	REIMBURSMENT	1	1	0.333	Not Outlier	22-04-2016	RR-HS16-10348180	01/08/2016	102	-
ASHISH HALLAN	100169	AASTHA LOK HOSPITAL PVT LTD	HEGIC-HS-21040	Primary	M48.5	-	1	1	0.333	Not Outlier	06-02-2016	RC-HS15-10336224	01/08/2016	178	-
ASHISH HALLAN	100169	AASTHA LOK HOSPITAL PVT LTD	HEGIC-HS-21040	Primary	M54.5	REIMBURSMENT	1	1	0.333	Not Outlier	22-04-2016	RR-HS16-10348180	01/08/2016	102	-
ASHISH HALLAN	100169	AASTHA LOK HOSPITAL PVT LTD	HEGIC-HS-21040	Primary	S69.9	-	0	1	0.000	Not Outlier	01-12-2015	RC-HS15-10325749	01/08/2016	245	-
ASHISH HALLAN	100169	AASTHA LOK HOSPITAL PVT LTD	Total			REIMBURSMENT	3	3	1.000	Outlier	22-04-2016	-	01/08/2016	102	-

good morning

this is my table

i am facing a problem in max_date and in clm_no

when same two date are coming its not able to show clm_no

codes :

max_date :

=Date(Max(aggr(Max(if(len(KeepChar(CLM_XGEN_CLAIM_NUM,'-'))<>3,CLM_INTIMATION_DATE)),

POL_BDM_CODE,POL_BDM_NAME,HOSPITAL_CODE,HOSPITAL_NAME,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org)),'DD-MM-YYYY')

and for clm_no :

=FirstSortedValue(CLM_XGEN_CLAIM_NUM,-aggr(Max(if(len(KeepChar(CLM_XGEN_CLAIM_NUM,'-'))<>3,CLM_INTIMATION_DATE)),

POL_BDM_CODE,POL_BDM_NAME,HOSPITAL_CODE,HOSPITAL_NAME,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

note:

max date along with is clm_no

output should be this :

POL_BDM_NAME

POL_BDM_CODE

HOSPITAL_NAME

HOSPITAL_CODE

CLM_LEVEL_OF_CARE

ICD_CODE_LEVEL1_org

Claim_Type

ICD Code Blacklist

total count of claims

Value

status

Max_date

clm_no

Current_date

Differnce

clm_num

ASHISH HALLAN

100169

AASTHA LOK HOSPITAL PVT LTD

HEGIC-HS-21040

Primary

I10

REIMBURSMENT

1

1

0.333

Not Outlier

22-04-2016

RR-HS16-10348180

01/08/2016

102

-

ASHISH HALLAN

100169

AASTHA LOK HOSPITAL PVT LTD

HEGIC-HS-21040

Primary

M48.5

-

1

1

0.333

Not Outlier

06-02-2016

RC-HS15-10336224

01/08/2016

178

-

ASHISH HALLAN

100169

AASTHA LOK HOSPITAL PVT LTD

HEGIC-HS-21040

Primary

M54.5

REIMBURSMENT

1

1

0.333

Not Outlier

22-04-2016

RR-HS16-10348180

01/08/2016

102

-

ASHISH HALLAN

100169

AASTHA LOK HOSPITAL PVT LTD

HEGIC-HS-21040

Primary

S69.9

-

0

1

0.000

Not Outlier

01-12-2015

RC-HS15-10325749

01/08/2016

245

-

ASHISH HALLAN

100169

AASTHA LOK HOSPITAL PVT LTD

Total

REIMBURSMENT

3

3

1.000

Outlier

22-04-2016

RR-HS16-10348180

01/08/2016

102

-

so can u help me this as fast u can

[Anil_Babu_Samineni]

Please upload file which demonstrates the issue

[sagarrahul]

attachment of

[Anil_Babu_Samineni]

What do you want to achieve for clm_num????

In your expression what is the use of this -- CLM_XGEN_CLAIM_NUM

But, In your data you don't have that Column

[sagarrahul]

ty anil

see there are two column ie max_date and clm_no

in max_date i want max_date and when max_date comes it should show its clm_no with it