topic Re: If statement in SQL Select - Syntax in QlikView

If statement in SQL Select - Syntax

sculptorlv — Mon, 07 Dec 2015 06:49:07 GMT

Hello, got problem with syntax. Please help.

I'm trying to get additional column in my table with conditional value 0 or 1 (True or False) - I do it for the first time.

SQL SELECT

...

SQL SELECT

Tabl_contract.Code AS "Contract_Number",

cle."Customer No_" AS "Customer_Number2",

cle."Document No_" AS "Debitor_document",

IF ((cle."Document No_" > 0), 1, 0) AS "Debitor_Sign"

FROM "EUR-VENDEN".dbo."EUR VENDEN$Cust_ Ledger Entry" AS cle

...

And I got error in IF statement..

IF ((cle."Document No_" > 0), 1, 0) AS "Debitor_Sign"

Re: If statement in SQL Select - Syntax

henrikalmen — Wed, 16 Dec 2015 15:06:44 GMT

It seems that you are trying to mix SQL with QlikView scripting language.

You should probably use Preceding Load and do something like this:

MyTable:

LOAD

IF ([Debitor_document] > 0), 1, 0) AS Debitor_Sign

;

SQL SELECT

Tabl_contract.Code AS Contract_Number,

cle.[Customer No_] AS Customer_Number2,

cle.[Document No_] AS Debitor_document

FROM "EUR-VENDEN".dbo."EUR VENDEN$Cust_ Ledger Entry" As cle ...

join ... as Tabl_contract;

EDIT: I just realised I entered the wrong field name in the preceding load. I have changed that now.

Re: If statement in SQL Select - Syntax

maxgro — Wed, 16 Dec 2015 19:03:27 GMT

you can use a case in sql server

SQL SELECT

Tabl_contract.Code AS "Contract_Number",

cle."Customer No_" AS "Customer_Number2",

cle."Document No_" AS "Debitor_document",

case "Debitor_Sign"=

when cle."Document No_" > 0 then 1

else 0

end

.....

FROM "EUR-VENDEN".dbo."EUR VENDEN$Cust_ Ledger Entry" AS cle

https://msdn.microsoft.com/it-it/library/ms181765(v=sql.120).aspx

Re: If statement in SQL Select - Syntax

sculptorlv — Thu, 17 Dec 2015 12:23:06 GMT

I tried to do this with CASE, didn't work

Re: If statement in SQL Select - Syntax

henrikalmen — Thu, 17 Dec 2015 13:08:50 GMT

Have you tried my suggested preceding load solution?

Re: If statement in SQL Select - Syntax

henrikalmen — Fri, 18 Dec 2015 08:44:00 GMT

I have updated that solution now...

BTW, using case I think you should write your statement like this instead:

CASE

WHEN [Document No_] > 0 THEN 1

ELSE

END AS Debitor_Sign

Re: If statement in SQL Select - Syntax

sculptorlv — Fri, 18 Dec 2015 08:50:29 GMT

No I didn't. Got problems with that, still trying to understand this function by manuals.

Re: If statement in SQL Select - Syntax

sculptorlv — Fri, 18 Dec 2015 12:37:44 GMT

This would not work.

Actually I have doubled rows this the same Document No_ value. And there are two only options for Applied column.

1. Both Applied volumes = 0 for each of two Document No_ value

2. First Applied =1, second Applied = 0 ..

Thus, I need only ONE row of Document No_ with the value Applied 1 (if one of the value =1) or 0 (if both Applied values = 0)

Re: If statement in SQL Select - Syntax

henrikalmen — Fri, 18 Dec 2015 13:30:57 GMT

I'm not quite sure what you are trying to do. Can you post some sample data? What the tables look like, some data in them, and what you would want the result to look like.