Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
andrea0901
Not applicable

Function not displaying values in straight table

Hi All,

 

i have written a flag conditions:

If(Len(Trim(EKPO.MATNR)) = 0, 1, 0) as MATNR_Flag,
if(len(trim(PIR_KBETR))=0,1,0) as Price_Flag,

Basically i am checking if my EKPO.MATNR is blank, then i need how many are null and how many or not.

After this i have created a straight Table as below:

 

SourceNulls in MATNRNotnull- MatnrTotal MatnrNullMatnr%NotnullMatnr%Nulls-PriceNotnulls - pricenotnullprice%
ABC68161564841633000.041739130.9582608701234100%
DEF5677123469110.821444070.1785559250345345100%
GHI789674567835340.945327650.054672349034345100%
JKL789566764560.12221190.87778810400-
MNO678567874630.909151820.0908481840234234100%

 

In Nulls in MTNR : i have written the code as 

=Count({$<MATNR_Flag = {'1'}>} EKPO.MATNR)

in Notnull - Matnr: i have written code as :

=Count({$<MATNR_Flag = {'0'}>} EKPO.MATNR)


In NullMatnr% column i have given this code and it is working:

=Count({$<MATNR_Flag = {'1'}>} EKPO.MATNR)/Count({$<MATNR_Flag =>} EKPO.MATNR) 

same for NotnullMatnr% : =Count({$<MATNR_Flag = {'0'}>} EKPO.MATNR)/Count({$<MATNR_Flag =>} EKPO.MATNR) 

 

 

But the same way i am wrtting the code for Nulls- Price and the rest of columns i am not getting the correct values.

its all showing 0 . 

Please help me out.

 

Regards

Andrea.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Function not displaying values in straight table

For example

PIR_KBETR,    Price_Flag

abc, 0

, 1

, 1

adgs, 0

Now, if you do Sum(Price_Flag) it should be 2 and not 4, right? Because Sum of 0, 1, 1, 0 = 2... count will be 4, but not sum

7 Replies
anushree1
Not applicable

Re: Function not displaying values in straight table

Why are you passing the values in quotes like '0' and '1' within set , they are  defined 1 and 0 in the script right?

andrea0901
Not applicable

Re: Function not displaying values in straight table

yes, i am taking in the front end as well. 

sunny_talwar
Not applicable

Re: Function not displaying values in straight table

For counting nulls, why don't you just try this

Sum(Price_Flag)
andrea0901
Not applicable

Re: Function not displaying values in straight table

Hi Sunny,

 

I tried sum(Price_Flag) it is counting all 1's and giving the values.

I need to count Null and Not null values and used the following code as below:

=Count({$<Price_Flag = {'1'}>} PIR.KBETR) but i am getting the all values as Zero

For Not null , i have written, =Count({$<Price_Flag = {'0'}>} PIR.KBETR)  for this expression i am gettign notnull values.

and then calculating the percentage as below:

Count({$<Price_Flag = {'1'}>} PIR.KBETR)/Count({$<Price_Flag =>} PIR.KBETR)  this is also  not working.

sunny_talwar
Not applicable

Re: Function not displaying values in straight table

But isn't Price_Flag = 1 when PIR_KBETR is Null and equals 0 when PIR_KBETR is not null? If this is true Sum(Price_Flag) should equal to the number of places when PIR_KBETR is null, right?

sunny_talwar
Not applicable

Re: Function not displaying values in straight table

For example

PIR_KBETR,    Price_Flag

abc, 0

, 1

, 1

adgs, 0

Now, if you do Sum(Price_Flag) it should be 2 and not 4, right? Because Sum of 0, 1, 1, 0 = 2... count will be 4, but not sum

andrea0901
Not applicable

Re: Function not displaying values in straight table

Hi sunny ,
Thanks a lot. It worked .