topic Re: Scripting If Statement / Case Statement in QlikView

Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:00:36 GMT

All

I'm having issues using the If statement on the scripting back end of QlikView

Can somebody explain why this doesn't work?

If Left([Insurer Policy Number],7) = 'GSPC701' Then 'Skippy'

Else 'Sampras'

End If as [Test],

Thanks in advance

Re: Scripting If Statement / Case Statement

Anonymous — Thu, 21 Nov 2013 12:07:23 GMT

James

Your syntax for a QlikView script was adrift, try something like this:

If ( Left([Insurer Policy Number],7) = 'GSPC701' ,'Skippy' ,'Sampras' ) as [Test] ,

Best Regards, Bill

Re: Scripting If Statement / Case Statement

israrkhan — Thu, 21 Nov 2013 12:08:30 GMT

Only this part.

If Left([Insurer Policy Number],7) = 'GSPC701' , 'Skippy', 'Sampras') as [Test]

That is SQL Case Statment Syntax...

Re: Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:16:22 GMT

I am putting this in a SQL selectstatement. But it doesn’t work (see below)

SQL SELECT

If ( Left([Insurer Policy Number],7) = 'GSPC701','Skippy' ,'Sampras' ) as [Test] ,
*

FROM SkippyReporting.dbo.Core_Product_All_Txns;

Re: Scripting If Statement / Case Statement

Anonymous — Thu, 21 Nov 2013 12:22:33 GMT

James

Ahh, I misunderstood you I thought you were talking about a QV Load Script statement.

What brand of database are you trying to do this SQL query against ?

Best Regards, Bill

Re: Scripting If Statement / Case Statement

CELAMBARASAN — Thu, 21 Nov 2013 12:24:06 GMT

You cannot use Qlikview expression in sql statement rather you can use it on preceding load.

try this

Load *,

If ( Left([Insurer Policy Number],7) = 'GSPC701','Skippy' ,'Sampras' ) as [Test];

SQL SELECT

*
FROM SkippyReporting.dbo.Core_Product_All_Txns;

Re: Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:25:07 GMT

Apologies

it is a QV load script. this is the entie script below.

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO lassie (XUserId is GaLDOJdJDaSYFVFMNLZGHXUNBbaKGYVMBbYKWdA);

SQL SELECT

If ( Left([Insurer Policy Number],7) = 'GSPC701' ,'Skippy' ,'Sampras' ) as [Test] ,
*

FROM SkippyReporting.dbo.Core_Product_All_Txns;

Re: Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:31:26 GMT

I just tried this to no avail.

ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Load
*,
     If ( Left([Insurer Policy Number],7) = 'GSPC701','Skippy' ,'Sampras' ) as [Test]
     ;
ODBC CONNECT TO lassie (XUserId is GaLDOJdJDaSYFVFMNLZGHXUNBbaKGYVMBbYKWdA);
SQL SELECT

     *
FROM SkippyReporting.dbo.Core_Product_All_Txns;

Re: Scripting If Statement / Case Statement

CELAMBARASAN — Thu, 21 Nov 2013 12:31:44 GMT

Qlikview execute the statement after the SQL word in the database only not within qlikview, so qlikview expressions cannot used there.

Re: Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:34:29 GMT

Can you suggest a solution?

Thanks for your help

Re: Scripting If Statement / Case Statement

tresB — Thu, 21 Nov 2013 12:45:01 GMT

Have you tried this?

Load *,

If ( Left([Insurer Policy Number],7) = 'GSPC701' ,'Skippy' ,'Sampras' ) as Test ;

SQL SELECT

FROM SkippyReporting.dbo.Core_Product_All_Txns;

Re: Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:46:45 GMT

Hi, It's like this.

LOAD

If (Left([Insurer Policy Number],7) = 'GSPC701','Skippy', 'Sampras')as [Test]

from file;

Regards.

Re: Scripting If Statement / Case Statement

CELAMBARASAN — Thu, 21 Nov 2013 12:53:44 GMT

My previous post has the solution, didn't you tried that?

Load *,

If ( Left([Insurer Policy Number],7) = 'GSPC701','Skippy' ,'Sampras' ) as [Test];

SQL SELECT

*
FROM SkippyReporting.dbo.Core_Product_All_Txns;

Re: Scripting If Statement / Case Statement

Thu, 21 Nov 2013 12:56:09 GMT

this is my existing code. how do I factor your suggestions into this?

ODBC CONNECT TO lassie (XUserId is GaLDOJdJDaSYFVFMNLZGHXUNBbaKGYVMBbYKWdA);

SQL SELECT
*
FROM SkippyReporting.dbo.Core_Product_All_Txns;