topic Re: Function not displaying values in straight table in QlikView

Function not displaying values in straight table

andrea0901 — Sat, 16 Nov 2024 04:32:00 GMT

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:

Source	Nulls in MATNR	Notnull- Matnr	Total Matnr	NullMatnr%	NotnullMatnr%	Nulls-Price	Notnulls - price	notnullprice%
ABC	6816	156484	163300	0.04173913	0.95826087	0	1234	100%
DEF	5677	1234	6911	0.82144407	0.178555925	0	345345	100%
GHI	78967	4567	83534	0.94532765	0.054672349	0	34345	100%
JKL	789	5667	6456	0.1222119	0.877788104	0	0	-
MNO	6785	678	7463	0.90915182	0.090848184	0	234234	100%

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.

Re: Function not displaying values in straight table

anushree1 — Thu, 07 Feb 2019 09:36:57 GMT

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

Re: Function not displaying values in straight table

andrea0901 — Thu, 07 Feb 2019 13:04:53 GMT

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

Re: Function not displaying values in straight table

sunny_talwar — Thu, 07 Feb 2019 13:28:00 GMT

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

Sum(Price_Flag)

Re: Function not displaying values in straight table

andrea0901 — Thu, 07 Feb 2019 14:30:19 GMT

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.

Re: Function not displaying values in straight table

sunny_talwar — Thu, 07 Feb 2019 14:34:32 GMT

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?

Re: Function not displaying values in straight table

sunny_talwar — Thu, 07 Feb 2019 14:35:55 GMT

For example

PIR_KBETR, Price_Flag

abc, 0

, 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

Re: Function not displaying values in straight table

andrea0901 — Fri, 08 Feb 2019 02:49:17 GMT

Hi sunny ,
Thanks a lot. It worked .