Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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

5 Replies
TigerisBack
Champion
Champion

  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
Author

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.

TigerisBack
Champion
Champion

ok inace of  * ,u can use ur fieldname

Not applicable
Author

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

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