topic Re: IF, IsNull in Francophones

IF, IsNull

Wed, 29 Oct 2014 17:53:25 GMT

Hi,

Is this syntax is correct?

DIM_PAYS :

LOAD "PAY_CODE",

IF (isnull("PAY_NOM"),PAY_NOM,'') AS 'Pays',

"DEV_CODE";

SQL SELECT PAY_CODE,

PAY_NOM,

DEV_CODE

FROM ICP.dbo.PAYS;

Thanks

Regards.

Re: IF, IsNull

maxgro — Wed, 29 Oct 2014 17:59:50 GMT

I think yes

You can remove some double and single quotes and maybe replace isnull with len(trim(PAY_NOM))>0

DIM_PAYS :

LOAD

PAY_CODE,

IF (len(trim(PAY_NOM))>0,PAY_NOM,'') AS Pays,

DEV_CODE;

SQL SELECT

PAY_CODE,

PAY_NOM,

DEV_CODE

FROM ICP.dbo.PAYS;

Re: IF, IsNull

Gabriel — Wed, 29 Oct 2014 18:00:10 GMT

Hi,

Is PAY_CODE a field?

If PAY_CODE is a field then you don't need to wrap it in double quote (""), just leave the field as PAY_CODE in side ISNULL(PAY_CODE)

Hope this helps

Gabriel

Re: IF, IsNull

its_anandrjs — Wed, 29 Oct 2014 18:08:14 GMT

If PAY_CODE is field and in the field name there is no space then do not use the (Double Quote) then use it.

DIM_PAYS :

LOAD PAY_CODE,

IF (isnull(PAY_NOM) = -1 , PAY_NOM,'') AS Pays,

DEV_CODE;

SQL SELECT PAY_CODE,

PAY_NOM,

DEV_CODE

FROM ICP.dbo.PAYS;

Regards

Anand

Re: IF, IsNull

Wed, 29 Oct 2014 18:22:03 GMT

Thank you to all

I have an other question, please.
When, we have a test between 2 tables (dimensions), how would I do to make this test?

for exemple i have ARTICLES and TABLES,

SELECT TBL_LIB FROM TABLES TA

INNER JOIN ARTICLES A ON TA.TBL_CODE = A.FA4_CODE

WHERE TA.TBL_ID = 'FA4';

Where do I make this request and how?

Regards

Re: Re: IF, IsNull

its_anandrjs — Wed, 29 Oct 2014 18:34:17 GMT

If you asking for this expression then try

TA:

Load

TBL_ID,

TBL_LIB,

TBL_CODE

FROM TA

Where TBL_ID = 'FA4';

Inner Join

ARTICLES:

Load

FA4_CODE

From ARTICLES;

Hope this helps

Anand

Re: IF, IsNull

Wed, 29 Oct 2014 18:47:43 GMT

thank you very much Anand,

How can I do, if we have "IsNull"?

Here is my request :
ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA4' AND TA.TBL_CODE = A.FA4_CODE), '') AS 'C4 Article',

Thank you for all.

Regards

slim

Re: IF, IsNull

its_anandrjs — Wed, 29 Oct 2014 19:08:41 GMT

For this use ISNULL individually to the fields and then use your expression, for more you have to use the NULL handling in the Qlikview, Your expression is in pure SQL you have to convert in into qlikview and then handle the NULL values and remove them.

Regards

Anand

Re: IF, IsNull

Wed, 29 Oct 2014 19:26:10 GMT

Thank you Anand,
but i'm beginner on Qlikview, I don't know, how i can to handle the NULL values and remove them.

what's the NULL handling in the Qlikview?

how can i use IsNull Individually to the fields and then use your expression?? have you an example please?

Thanks.

Re: IF, IsNull

its_anandrjs — Wed, 29 Oct 2014 19:32:26 GMT

For Null handling please read this document

NULL handling in QlikView

Regards

Anand