Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
this is my table
>>>>>>>>>>>>>>>>>>>>
this are my column
1)HOSPITAL_NAME
2)HOSPITAL_CODE
3)CLM_XGEN_CLAIM_NUM
4)CLM_INTIMATION_DATE
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
HOSPITAL_NAME HOSPITAL_CODE CLM_XGEN_CLAIM_NUM CLM_INTIMATION_DATE
'Dr.Agarwal's Eye hospital' HEGIC-HS-03644 RC-HS15-10334760 28-Jan-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03644 RC-HS15-10335711 03-Feb-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03660 RC-HS15-10341139 08-Mar-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03663 RC-HS15-10343361 22-Mar-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03663 RC-HS16-10352751 20-May-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03666 RC-HS15-10336529 08-Feb-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03674 RC-HS16-10350007 04-May-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03679 RC-HS15-10336520 08-Feb-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03679 RC-HS15-10337526 15-Feb-2016
'Dr.Agarwal's Eye hospital' HEGIC-HS-03680 RC-HS16-10352247 17-May-2016
'Dr.Agarwal's Eye hospital'
4S CARE HOSPITAL HEGIC-NN-71593 RR-HS15-10336940 11-Feb-2016
4S CARE HOSPITAL
7 STAR SUPER SPECIALITY HOSPITAL HEGIC-NN-42555 RR-HS15-10300335-1 05-Apr-2016
7 STAR SUPER SPECIALITY HOSPITAL
A J HOSPITAL AND RESEARCH CENTRE HEGIC-NN-28935 RR-HS15-10341978 14-Mar-2016
A J HOSPITAL AND RESEARCH CENTRE
A N N HOSPITAL HEGIC-HS-04465 RC-HS15-10333637 20-Jan-2016
A N N HOSPITAL HEGIC-HS-04465 RR-HS15-10341246 09-Mar-2016
A N N HOSPITAL
A ONE HOSPITAL PVT LTD HEGIC-HS-01905 RR-HC16-10349105 28-Apr-2016
A ONE HOSPITAL PVT LTD
A TUNGEKAR HOSPITAL HEGIC-NN-75296 RR-HS16-10351758 14-May-2016
A TUNGEKAR HOSPITAL
A V HOSPITAL HEGIC-HS-02596 RC-HS15-10331178 05-Jan-2016
A V HOSPITAL
A. N. N. HOSPITAL HEGIC-NN-71509 RR-HS15-10336696 09-Feb-2016
A. N. N. HOSPITAL
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS15-10343151 21-Mar-2016
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS15-10343151-1 02-Apr-2016
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS16-10346029 08-Apr-2016
A.G PADMAVATI'S HOSPITAL
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
in this i want follwing things in it .
1) i want distinct count of CLM_XGEN_CLAIM_NUM
there should be no suppplementary clm in it
eg :
RC-HS15-10343151
RC-HS15-10343151-1
RC-HS16-10346029
T20TTT
so count should be = 2
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2) adding this as wildmatch
('T02.9','T02.8','T02.7','T02.6','T02.5','T02.4','T02.3','S43.3','S43.2','S43.1','S43.0','S42.9','S42.8','S42.7','S42.4','S42.3','S42.2',
'S42.1','S42.0','S62.8','S62.7','S62.6','S62.5','S62.4','S62.3','S62.2','S62.1','S62.0','S22.8','S22.5','S55.9','S55.8','S55.7','S55.1','S55.0','S32.7','S32.5','S32.4',
'S32.3','S32.2','S32.1','S32.0','S52.4','S52.3','S52.2','S52.1','S52.0','S93.2','S92.9','S92.7','S92.5','S92.4','S92.3','S92.2','S92.1','S92.0','S02.9','S02.8','S02.7',
'S02.6','S02.5','S02.4','S02.3','S02.2','S02.1','S34.1','T93.2','T90.2',' ','B97.3','S65.2','S65.0','S45.1','S45.0','S44.8','S44.7','S44.4','S44.3','S44.2','S44.1',
'S44.0','S82.7','S82.6','S82.5','S82.4','S82.3','S82.2','S82.1','S82.0','T93.1','T92.9','T92.8','T92.5','T92.4','T92.3','S59.7','A18.0','S64.2','S64.1','S64.0','S23.1',
'S22.9','S54.9','S54.8','S54.7','S54.2','S54.1','S54.0','S53.1','S53.0','S52.9','S52.8','S52.7','S52.6','T10','M45','T12','T08','S52.5','S89.7','T92.2','T92.1','S73.1',
'S73.0','S72.9','S72.8','S72.7','S72.4','S72.3','S72.2','S72.1','S33.2','S33.1','S22.1','S22.0','S22.4','S22.3','S22.2','Q66.1','Q66.0','M96.6','M84.3','M84.2','M84.1',
'M84.0','M20.1','M67.4','M54.5','M54.3','M01.1 ','M01.0 ','M00.9','M51.9','M51.8','M51.3','M51.2','M51.1','M51.0','M46.2','M49.0 ','M23.0','M25.7','M25.6','M25.4','M90.0',
'M89.3','M25.1','M25.0','M24.6','M24.5','M24.4','M24.0','M23.4','M48.5','M86.9','M86.8','M86.6','M86.5','M86.4','M86.3','M86.2','M86.1','M86.0','M85.6','M85.5','M85.4',
'M22.2','M22.1','M22.0','M21.6','M21.5','M21.4','M21.3','M21.2','M21.1','M21.0')
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3)then total of indivual code that are in wild match / total of CLM_XGEN_CLAIM_NUM in a particular hospital
and if the icd codes means yhat we have given in wildmatch if some item get repeated then it should total that particlar code and then divede with total of CLM_XGEN_CLAIM_NUM
eg :
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS15-10343151 21-Mar-2016 1 S43.0 1
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS15-10343151-1 02-Apr-2016
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS16-10346029 08-Apr-2016 1 S43.0 1
A.G PADMAVATI'S HOSPITAL Total 2 2 1
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
what i want in output :
"""""""""""""""""""""
HOSPITAL_NAME HOSPITAL_CODE CLM_XGEN_CLAIM_NUM CLM_INTIMATION_DATE icd total_icd_code total_clm value
'Dr.Agarwal's Eye hospital' HEGIC-HS-03644 RC-HS15-10334760 28-Jan-2016 T02.4 1 1 0.1
'Dr.Agarwal's Eye hospital' HEGIC-HS-03644 RC-HS15-10335711 03-Feb-2016 M51.9 3 1 0.3
'Dr.Agarwal's Eye hospital' HEGIC-HS-03660 RC-HS15-10341139 08-Mar-2016 S53.1 3 1 0.3
'Dr.Agarwal's Eye hospital' HEGIC-HS-03663 RC-HS15-10343361 22-Mar-2016 S43.3 1 1 0.1
'Dr.Agarwal's Eye hospital' HEGIC-HS-03663 RC-HS16-10352751 20-May-2016 S53.1 3 1 0.3
'Dr.Agarwal's Eye hospital' HEGIC-HS-03666 RC-HS15-10336529 08-Feb-2016 S82.1 1 1 0.1
'Dr.Agarwal's Eye hospital' HEGIC-HS-03674 RC-HS16-10350007 04-May-2016 T90.0 1 1 0.1
'Dr.Agarwal's Eye hospital' HEGIC-HS-03679 RC-HS15-10336520 08-Feb-2016 M51.9 3 1 0.3
'Dr.Agarwal's Eye hospital' HEGIC-HS-03679 RC-HS15-10337526 15-Feb-2016 S53.1 3 1 0.3
'Dr.Agarwal's Eye hospital' HEGIC-HS-03680 RC-HS16-10352247 17-May-2016 M51.9 3 1 0.3
'Dr.Agarwal's Eye hospital' Total 10
4S CARE HOSPITAL HEGIC-NN-71593 RR-HS15-10336940 11-Feb-2016 T02.4 1 1 1
4S CARE HOSPITAL Total 1 1
7 STAR SUPER SPECIALITY HOSPITAL HEGIC-NN-42555 RR-HS15-10300335-1 05-Apr-2016 M51.9 1 0 0
7 STAR SUPER SPECIALITY HOSPITAL Total 1 0
A J HOSPITAL AND RESEARCH CENTRE HEGIC-NN-28935 RR-HS15-10341978 14-Mar-2016 S53.1 1 1 1
A J HOSPITAL AND RESEARCH CENTRE Total 1 1
A N N HOSPITAL HEGIC-HS-04465 RC-HS15-10333637 20-Jan-2016 S43.3 1 1 0.5
A N N HOSPITAL HEGIC-HS-04465 RR-HS15-10341246 09-Mar-2016 S53.1 1 1 0.5
A N N HOSPITAL Total 1 2
A ONE HOSPITAL PVT LTD HEGIC-HS-01905 RR-HC16-10349105 28-Apr-2016 M48.5 1 1 1
A ONE HOSPITAL PVT LTD Total 1 1
A TUNGEKAR HOSPITAL HEGIC-NN-75296 RR-HS16-10351758 14-May-2016 M48.5 1 1 1
A TUNGEKAR HOSPITAL Total 1 1
A V HOSPITAL HEGIC-HS-02596 RC-HS15-10331178 05-Jan-2016 M48.5 1 1 1
A V HOSPITAL Total 1 1
A. N. N. HOSPITAL HEGIC-NN-71509 RR-HS15-10336696 09-Feb-2016 T02.4 1 1 1
A. N. N. HOSPITAL Total 1 1
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS15-10343151 21-Mar-2016 S53.1 1 1 0.5
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS15-10343151-1 02-Apr-2016 S53.0 1 0
A.G PADMAVATI'S HOSPITAL HEGIC-HS-01854 RC-HS16-10346029 08-Apr-2016 S43.3 1 1 0.5
A.G PADMAVATI'S HOSPITAL Total 2 2
Can you please provide your sample data in some excel file ?