Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

If statement in SQL Select

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

Tags (4)
5 Replies
SunilChauhan
Not applicable

If statement in SQL Select

  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]
;

Not applicable

If statement in SQL Select

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.

SunilChauhan
Not applicable

If statement in SQL Select

ok inace of  * ,u can use ur fieldname

Not applicable

If statement in SQL Select

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

chriscammers
Not applicable

Re: If statement in SQL Select

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')