max date along with clm_no

[sagarrahul]

this is my table below

AGENT_FULL_NAME	POL_AGENT_CODE	HOSPITAL_NAME	HOSPITAL_CODE	CLM_INTIMATION_DATE	CLM_XGEN_CLAIM_NUM	CLM_LEVEL_OF_CARE
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	04-Jan-2016	RC-HS15-10325506-1	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	30-Nov-2015	RC-HS15-10325506	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	24-Dec-2015	RC-HS15-10329647	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	06-Dec-2015	RC-HS15-10326507	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	26-Jan-2016	RC-HS15-10325506-2	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	01-May-2016	RC-HS16-10349544	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	HEGIC-HS-02071	31-May-2016	RC-HS16-10349544-1	Secondary
HE DIRECT 1000 -3420	200278133519	AASTHA HOSPITAL	Total

ICD_CODE_LEVEL1_org	Claim_Type	ICD Code Blacklist	count of claims	Value	status	Max_date	clm_no	Current_date	Differnce	clm_num
J22	-	1	0	-	Not Outlier	04-Jan-2016	RC-HS15-10325506-1	18/07/2016	197	-
J22	-	1	1	1.000	Outlier	30-Nov-2015	RC-HS15-10325506	18/07/2016	232	-
A01.0	-	1	1	1.000	Outlier	24-Dec-2015	RC-HS15-10329647	18/07/2016	208	-
J22	-	1	1	1.000	Outlier	06-Dec-2015	RC-HS15-10326507	18/07/2016	226	-
J22	-	1	0	-	Not Outlier	26-Jan-2016	RC-HS15-10325506-2	18/07/2016	175	-
N39.0	-	0	1	0.000	Not Outlier	01-May-2016	RC-HS16-10349544	18/07/2016	79	-
N39.0	-	0	0	-	Not Outlier	31-May-2016	RC-HS16-10349544-1	18/07/2016	49	-
	-	5	4	1.250	Outlier	31-May-2016	RC-HS16-10349544-1	18/07/2016	49	-

in this table till yellow color marked are showing proper ans as i want bt whn its coming to show max date its not showing what i want

i want it like this max date should show only of main claims date only not of supplementary claims and in clm_no it should main claims

along with max date

this what i want

Max_date	clm_no
04-Jan-2016	RC-HS15-10325506-1
30-Nov-2015	RC-HS15-10325506
24-Dec-2015	RC-HS15-10329647
06-Dec-2015	RC-HS15-10326507
26-Jan-2016	RC-HS15-10325506-2
01-May-2016	RC-HS16-10349544
31-May-2016	RC-HS16-10349544-1
01-May-2016	RC-HS16-10349544

expression are used by me are :

for max date is =max(CLM_INTIMATION_DATE)

and for clm_no is =MaxString(aggr(MaxString(CLM_XGEN_CLAIM_NUM),HOSPITAL_CODE,HOSPITAL_NAME,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

so any1 can help me in this

its on urgent basics

[arulsettu]

format your date like this then use max function

date(Date#(CLM_INTIMATION_DATE,'DD-MMM-YYYY'),'DD/MM/YYYY') as customdate

[jonathandienst]

>>should show only of main claims date only not of supplementary claims...

How do you identify which lines are main and which are supplementary? You can use this field in a set expression in your table, using experession like

Max({<Type = {'Main'}>} CLM_INTIMATION_DATE)

Change the criterion in bold per your requirements

[sagarrahul]

i want max date in max_date column along with its only main clm_no not supplementary clms