Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]
;
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.
ok inace of * ,u can use ur fieldname
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 ',' .
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')