Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
format your date like this then use max function
date(Date#(CLM_INTIMATION_DATE,'DD-MMM-YYYY'),'DD/MM/YYYY') as customdate
>>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
i want max date in max_date column along with its only main clm_no not supplementary clms