topic Re: count diff value in single column in QlikView

count diff value in single column

sagarrahul — Wed, 25 Nov 2020 16:16:04 GMT

hiiii

need a help

Black List Code

H26.9

H25.9

H11.0

H26.9

in this column i have total 10 values

but in this there are different values

H26.9

H25.9

H11.0

now what i want is to count this individuals in different column and divide by 10

eg :

H26.9 =8

H25.9=1

H11.0=1

so anyone can help me in this

Re: count diff value in single column

tamilarasu — Wed, 15 Jun 2016 06:39:32 GMT

Hi,

Straight Table -> Add [Black List Code] as Dimension -> Expression as Count([Black List Code])

Re: count diff value in single column

sagarrahul — Wed, 15 Jun 2016 06:59:43 GMT

thanks you for your help

But on thing more Tamil nagaraj this Black List Code are coming from a expression where they are in a wild match

so now how to i bring it as i want in my above query

Re: count diff value in single column

tamilarasu — Wed, 15 Jun 2016 07:05:48 GMT

Sagar,

Could you please share some sample application. It would be helpful for me to provide the solution.

Re: count diff value in single column

sagarrahul — Wed, 15 Jun 2016 07:11:31 GMT

means what u want

Re: count diff value in single column

sagarrahul — Wed, 15 Jun 2016 07:12:26 GMT

see this table i am having need help me in this

HOSPITAL_NAME

HOSPITAL_CODE

CLM_XGEN_CLAIM_NUM

CLM_INTIMATION_DATE

CLM_LEVEL_OF_CARE

Claim_Type

total count of claims

Black List Code

ICD Code Blacklist

Value

status

max_date

Claim _no

Current_date

Differnce

Claim_num

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03644

RC-HS15-10334760

28-Jan-2016

Secondary

H26.9

1.0

Outlier

28-Jan-2016

RC-HS15-10334760

15-Jun-2016

140

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03644

RC-HS15-10335711

03-Feb-2016

Secondary

H26.9

1.0

Outlier

03-Feb-2016

RC-HS15-10335711

15-Jun-2016

134

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03660

RC-HS15-10341139

08-Mar-2016

Secondary

H26.9

1.0

Outlier

08-Mar-2016

RC-HS15-10341139

15-Jun-2016

100

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03663

RC-HS15-10343361

22-Mar-2016

Secondary

H26.9

1.0

Outlier

22-Mar-2016

RC-HS15-10343361

15-Jun-2016

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03663

RC-HS16-10352751

20-May-2016

Secondary

H26.9

1.0

Outlier

20-May-2016

RC-HS16-10352751

15-Jun-2016

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03679

RC-HS15-10336520

08-Feb-2016

Secondary

H26.9

1.0

Outlier

08-Feb-2016

RC-HS15-10336520

15-Jun-2016

129

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03679

RC-HS15-10337526

15-Feb-2016

Secondary

H26.9

1.0

Outlier

15-Feb-2016

RC-HS15-10337526

15-Jun-2016

122

'Dr.Agarwal's Eye hospital'

HEGIC-HS-03680

RC-HS16-10352247

17-May-2016

Secondary

H26.9

1.0

Outlier

17-May-2016

RC-HS16-10352247

15-Jun-2016

Re: count diff value in single column

susovan — Wed, 15 Jun 2016 07:19:30 GMT

Hi sagar,

Try this way,

Select your expression from "Expression" tab than do this change.

Re: count diff value in single column

tamilarasu — Wed, 15 Jun 2016 07:28:35 GMT

I meant your expression.

Re: count diff value in single column

jonathandienst — Wed, 15 Jun 2016 08:03:20 GMT

I am afraid that does not help. You say the blacklist code is an expression. How do you expect use to help if we do not know what that expression looks like? Did you try adapting tamilarasu's suggestion by using a calculated dimension and a suitably modified expression?

I suggest that you upload a sample qvw with some representative data.

Preparing examples for Upload - Reduction and Data Scrambling

Re: count diff value in single column

ramasaisaksoft — Wed, 15 Jun 2016 09:42:21 GMT

Hi Sagar,

Above yellow color (Show Frequency)will help ypu to specify no.of times the value repeated in a list box

so ,try to create the list box with your expression and select this option.

i hope it will help you.

Re: count diff value in single column

sagarrahul — Wed, 15 Jun 2016 12:15:32 GMT

these are my expression in above table :

1)claim_Type

only({<CLM_TYPE={'Reimbursment'}>}CLM_TYPE)

2)total count of claims

=count(Distinct if(len(KeepChar(CLM_XGEN_CLAIM_NUM,'-'))<>3,CLM_XGEN_CLAIM_NUM))

3)ICD Code Blacklist

=Count({<ICD_CODE_LEVEL1_org = { 'Z51.1','Y84.2','Y84.1','Y84.0','Y83.9','Z40.9','Z40.8','Z40.0','S83.6','S83.5','S83.4','S83.3','S83.2','S83.1','S83.0','D06.0','D05.9','D05.7','D24','D27',

'D10.1','D10.0','D09.9','N22.8'} >}

ICD_CODE_LEVEL1_org)

4)Black List Code

=if(WildMatch(ICD_CODE_LEVEL1_org ,'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','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'),ICD_CODE_LEVEL1_org)

5)value

=[ICD Code Blacklist] / [total count of claims]

6)status

=If(([Value]) >= 0.7, 'Outlier', 'Not Outlier')

7)Max date

=max(CLM_INTIMATION_DATE)

8)claim no

=MaxString(aggr(MaxString(CLM_REFERENCE_NUM),HOSPITAL_CODE,HOSPITAL_NAME,CLM_XGEN_CLAIM_NUM,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

9)Current date

=date(Today())

10)Differnce

=[Current date ]-[max_date]+1

11)Claim_num

=if((Differnce =1 ),CLM_REFERENCE_NUM)

now can u help me in this .

Re: count diff value in single column

tamilarasu — Wed, 15 Jun 2016 12:39:00 GMT

Create a straight table and use below expression as calculated dimension

=if(WildMatch(ICD_CODE_LEVEL1_org ,'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',

'M22.2','M22.1','M22.0','M21.6','M21.5','M21.4','M21.3','M21.2','M21.1','M21.0'),ICD_CODE_LEVEL1_org)

Go to expression tab and add expression as

Count(ICD_CODE_LEVEL1_org)

Re: count diff value in single column

jonathandienst — Wed, 15 Jun 2016 13:55:53 GMT

That should do the trick