topic If statement in SQL Select in QlikView

If statement in SQL Select

Fri, 30 Sep 2011 14:20:56 GMT

Hi all,

I'm having several problems trying to work with an old legacy data set and the only one that I can't seem to overcome is a date issue.

I need to create a YearMonth filed in my SQL Select statement so that I can then reference it in the where clause, but can not seem to get it right.

This is a "trimmed" version of what I am using (minus many of the fields):

[ClaimsHistory]:
first 10000 LOAD
          [Year], 
          [YearMonth],
          [Adjuster Checker], 
          [Adj Checker Desc], 
          [Name 1], 
          [Name 1 Qualifier], 
          [Name 2];
SQL Select 
          [Year], 
          [Month],
          IF CHAR([Month])= '1' THEN [Year]&'0'&[Month] ELSE [Year]&[Month] as [YearMonth],
          [Adjuster Checker], 
          [Adj Checker Desc], 
          [Name 1], 
          [Name 1 Qualifier], 
          [Name 2]
FROM DataMart.Class."Open_Claims_History";
//Where 'YearMonth' > '$(vLoadDate)';               <------ To be used once I can get the year month working.
Exit Script;

The main problem I face is that the month field contains 1,2,3,4......11,12. So I am trying to populate a '0' when the char count =1.

I'd be greaful for any help.

Many thanks,

Ronnie

If statement in SQL Select

SunilChauhan — Fri, 30 Sep 2011 14:38:45 GMT

you need to take resident like below

[ClaimsHistory]: first 10000 LOAD [Year], [YearMonth], [Adjuster Checker], [Adj Checker Desc], [Name 1], [Name 1 Qualifier], [Name 2]; SQL Select [Year], [Month], IF CHAR([Month])= '1' THEN [Year]&'0'&[Month] ELSE [Year]&[Month] as [YearMonth], [Adjuster Checker], [Adj Checker Desc], [Name 1], [Name 1 Qualifier], [Name 2] FROM DataMart.Class."Open_Claims_History"; //Where 'YearMonth' > '$(vLoadDate)'; <------ To be used once I can get the year month working.

[tablenamewhateveryouwant]:

load

1 as junk

resident [ClaimsHistory]Where 'YearMonth' > '$(vLoadDate)';

drop table [ClaimsHistory];

If statement in SQL Select

Fri, 30 Sep 2011 14:47:58 GMT

Thanks Sunil, but the problem is that I need to avoid loading * as there are many millions of rows per year.

I need the YearMonth field to allow me to create an incremental load so that I load everything that is newer than my current qvd data set.

There are 80 fields, many more calculated in this data extract and I am trying to make this as efficient as possible. By only pulling in a months worth of data at a time, I will be retreiving 500,000 rows for every hit, rather than 20 years worth of data each time.

If statement in SQL Select

SunilChauhan — Fri, 30 Sep 2011 14:51:24 GMT

ok inace of * ,u can use ur fieldname

If statement in SQL Select

Fri, 30 Sep 2011 14:58:40 GMT

The problem is that the if statement:

IF CHAR([Month])= '1' THEN [Year]&'0'&[Month] ELSE [Year]&[Month] as [YearMonth],

will not work in the select statement. I get this:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'THEN'.

I was hoping that someone would know the correct way of writing it.

If have tried writing it in the format of

IF (CHAR([Month])= '1', [Year] & '0' & [Month], [Year]&[Month]) as [YearMonth],

But the error message appears

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near ',' .

Re: If statement in SQL Select

chriscammers — Fri, 30 Sep 2011 17:10:48 GMT

You can do one of Three things

Change your If... Then ... Else or IF() syntax to a "Case" Statement in the SQL Select portion of the code

This is an approximation of the "Case" syntax, please use SQL Server help or google it

Here is a link I did a quick search for... http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/

Case

When Char([Month]) = '1'

Then [Year] & '0' & [Month]

Else [Year] & [Month]

End

OR you can move your IF() syntax to the Load portion of the Qlikview Code.

OR In the Load part of your statement you can use this expression

[Year] & Num([Month],'00')