Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sculptorlv
Creator III
Creator III

If statement in SQL Select - Syntax

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..

1.jpg

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

8 Replies
henrikalmen
Creator
Creator

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.

maxgro
MVP
MVP

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

sculptorlv
Creator III
Creator III
Author

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

henrikalmen
Creator
Creator

Have you tried my suggested preceding load solution?

henrikalmen
Creator
Creator

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

    0

END AS Debitor_Sign

sculptorlv
Creator III
Creator III
Author

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

sculptorlv
Creator III
Creator III
Author

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)

henrikalmen
Creator
Creator

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.