topic Re: if condition not working properly in QlikView

if condition not working properly

neha_sri — Sun, 18 May 2014 16:27:48 GMT

Hi everyone,

I am trying to run a query,but I am not getting the desired output.

LOAD

COMPONENT_NAME,

RESULT_VALUE,

TEST_ID,

if(upper(COMPONENT_NAME)='IRON AS FE',Count(if(RESULT_VALUE >=15,'5'))) as Ind

from result.qvd

Group by

COMPONENT_NAME,RESULT_VALUE,TEST_ID;

But everytime it get 0,1 as Ind

Is there any pblm ion my script???

Re: if condition not working properly

its_anandrjs — Sun, 18 May 2014 16:59:21 GMT

Your count expression is wrong in the load script that required the field name there

In this below line you have error in place of '5' you need field name

if(upper(COMPONENT_NAME)='IRON AS FE',Count(if(RESULT_VALUE >=15,'5'))) as Ind

That may be

if(upper(COMPONENT_NAME)='IRON AS FE',Count(if(RESULT_VALUE >=15,FieldName))) as Ind

If possible share the sample file.

Re: if condition not working properly

neha_sri — Sun, 18 May 2014 17:35:19 GMT

Hi,

The above given solution doesn't works.

my data is something like

COMPONENT_NAME RESULT_VALUE TEST_ID

IRON AS FE 18 299

MN 20 768

AG 21 578

IRON AS FE 24 765

IRON AS FE 4 676

I want to calculate only those values whosename is IRON AS FE and count is above 15 and name it as IND.The query I am using is.

LOAD

COMPONENT_NAME,

RESULT_VALUE,

TEST_ID,

if(upper(COMPONENT_NAME)='IRON AS FE',Count(if(RESULT_VALUE >=15,RESULT_VALUE ,))) as Ind

from result.qvd

Group by

COMPONENT_NAME,RESULT_VALUE,TEST_ID;

But I am getting oly 0,1 as IND.

Re: if condition not working properly

its_anandrjs — Sun, 18 May 2014 17:50:24 GMT

Update:- Now check i make some changes remove one dummy row

Try the below load script

Source:

LOAD * INLINE [

COMPONENT_NAME, RESULT_VALUE, TEST_ID

IRON AS FE, 18, 299

MN, 20, 768

AG, 21, 578

IRON AS FE, 24, 765

IRON AS FE, 4, 676

];

Final:

LOAD

COMPONENT_NAME,

RESULT_VALUE,

TEST_ID,

if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,Count(TEST_ID)) as Ind

Resident Source

Group by

COMPONENT_NAME,RESULT_VALUE,TEST_ID;

DROP Table Source;

Note:- But my question is why you Count the TEST_ID because it will gives you 1 for the TEST_ID if it is present think about this or may be you want to Sum the TEST_ID values then use like below

if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,Sum(TEST_ID)) as Ind2

Re: if condition not working properly

neha_sri — Sun, 18 May 2014 18:09:00 GMT

Hi,

I don't want to count the test values,rather I need the values of result_value whose value is greater then 15.I further need these values for further calculations.

Re: if condition not working properly

its_anandrjs — Sun, 18 May 2014 18:14:26 GMT

Ok then you have to use below script in load script replace with this

if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,RESULT_VALUE) as Ind

And let me know what is your expected out put from the sample data and why you use TEST_ID.

Re: if condition not working properly

neha_sri — Sun, 18 May 2014 18:15:53 GMT

I tried this,but it is not providing the correct output

Re: if condition not working properly

its_anandrjs — Sun, 18 May 2014 18:21:39 GMT

What is your expected out put from when you add below script

if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,RESULT_VALUE,0) as Ind

You get the below out put

for Ind field >>

Re: if condition not working properly

neha_sri — Sun, 18 May 2014 18:46:13 GMT

FINAL_RESULT:

LOAD

COMPONENT_NAME,

RESULT_VALUE,

TEST_ID,

if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,RESULT_VALUE) as Ind

FROM RESULT.qvd;

//Group by

//COMPONENT_NAME,RESULT_VALUE,TEST_ID;

exit Script;

//DROP TABLE RESULT;

It is working fine,But I want the count of RESULT_VALUE whose value >15 as Ind.

I am getting the values,but I only need the count not the values.

Re: if condition not working properly

its_anandrjs — Sun, 18 May 2014 20:48:54 GMT

Then you have to load a table same and add new field with count and aggregate the data see the script

NAL_RESULT:

LOAD

COMPONENT_NAME,

RESULT_VALUE,

TEST_ID,

if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,RESULT_VALUE) as Ind,

Count(if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,RESULT_VALUE)) as ResultCount

FROM RESULT.qvd;

Group by

COMPONENT_NAME,RESULT_VALUE,TEST_ID;

Exit Script;

DROP TABLE RESULT;

And then in front end Straight table chart

Dimension:- COMPONENT_NAME

Expr:- Sum(ResultCount)

For calculate the values or you can load another table also

Details:

LOAD

COMPONENT_NAME,

Count(if(upper(COMPONENT_NAME) = 'IRON AS FE' and RESULT_VALUE >= 15,RESULT_VALUE)) as Count

Resident Final

Group By COMPONENT_NAME;

Re: if condition not working properly

neha_sri — Mon, 19 May 2014 06:11:32 GMT

I tried the above mentioned query but count returns only 0 and 1,nothing else.

Re: if condition not working properly

jagan — Mon, 19 May 2014 06:21:42 GMT

Hi Neha,

I think it is not possible to calculate in backend, try like this

Load all the data and then use this Set Analysis expression

=Count({<COMPONENT_NAME={'IRON AS FE'}, RESULT_VALUE={'>15'}>} TEST_ID)

Regards,

jagan.

Re: if condition not working properly

neha_sri — Mon, 19 May 2014 07:36:37 GMT

Hi All,

I found the solution for this.

Final:

LOAD TEST_ID,

COMPONENT_NAME,

RESULT_VALUE,

if(upper(COMPONENT_NAME)='IRON AS FE' and RESULT_VALUE >=15,RESULT_VALUE) as Ind

FROM

[RESULT.qvd]

(qvd);

temp:

load distinct Ind as IND1

Resident Final Where Exists (Ind);

let v_var=NoOfRows('temp');

load distinct *,'$(v_var)' as Count resident Final;

Here we can get the count at backend only.

Re: if condition not working properly

jagan — Mon, 19 May 2014 08:37:10 GMT

Hi Neha,

Try this simple script

Source:

LOAD * INLINE [

COMPONENT_NAME, RESULT_VALUE, TEST_ID

IRON AS FE, 18, 299

MN, 20, 768

AG, 21, 578

IRON AS FE, 24, 765

IRON AS FE, 4, 676

];

Data:

LOAD

COMPONENT_NAME,

Count(TEST_ID) AS Result_GR_15

RESIDENT Source

WHERE COMPONENT_NAME = 'IRON AS FE' AND RESULT_VALUE > 15

GROUP BY COMPONENT_NAME;

Regards,

jagan.