Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarrahul
Creator
Creator

max date along with clm_no

this is my table below

       

AGENT_FULL_NAMEPOL_AGENT_CODEHOSPITAL_NAMEHOSPITAL_CODECLM_INTIMATION_DATECLM_XGEN_CLAIM_NUMCLM_LEVEL_OF_CARE
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207104-Jan-2016RC-HS15-10325506-1Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207130-Nov-2015RC-HS15-10325506Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207124-Dec-2015RC-HS15-10329647Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207106-Dec-2015RC-HS15-10326507Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207126-Jan-2016RC-HS15-10325506-2Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207101-May-2016RC-HS16-10349544Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207131-May-2016RC-HS16-10349544-1Secondary
HE DIRECT 1000 -3420200278133519AASTHA HOSPITALTotal


           

ICD_CODE_LEVEL1_orgClaim_TypeICD Code Blacklistcount of claimsValuestatusMax_dateclm_noCurrent_dateDiffernceclm_num
J22-10-Not Outlier04-Jan-2016RC-HS15-10325506-118/07/2016197-
J22-111.000Outlier30-Nov-2015RC-HS15-1032550618/07/2016232-
A01.0-111.000Outlier24-Dec-2015RC-HS15-1032964718/07/2016208-
J22-111.000Outlier06-Dec-2015RC-HS15-1032650718/07/2016226-
J22-10-Not Outlier26-Jan-2016RC-HS15-10325506-218/07/2016175-
N39.0-010.000Not Outlier01-May-2016RC-HS16-1034954418/07/201679-
N39.0-00-Not Outlier31-May-2016RC-HS16-10349544-118/07/201649-

-541.250Outlier31-May-2016RC-HS16-10349544-118/07/201649-

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_dateclm_no
04-Jan-2016RC-HS15-10325506-1
30-Nov-2015RC-HS15-10325506
24-Dec-2015RC-HS15-10329647
06-Dec-2015RC-HS15-10326507
26-Jan-2016RC-HS15-10325506-2
01-May-2016RC-HS16-10349544
31-May-2016RC-HS16-10349544-1
01-May-2016RC-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

3 Replies
arulsettu
Master III
Master III

format your date like this then use max function

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sagarrahul
Creator
Creator
Author

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