NewBie on Load, combine SQL statement

Thu, 18 Jun 2015 10:19:03 GMT

Hi, I understand from forum that I may use load to create calendar:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=USER;Initial Catalog=Report;Data Source=localDB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096; (XPassword is ABC);

Load

Min(Test_Date) as Date1,

Date(Test_Date,'DD-MM-YYYY') as Date2;

SQL SELECT "Field1", "Field2", "Test_Code", "Test_Date", "Field5", FROM Report.dbo."Table1";

The Problem is if I use

Min(Test_Date) as Date1, it will not be able to get data into qlikview

OLEDB read failed

SQL SELECT "Test_Date", "Test_Part", "Weekday" FROM Report.dbo."Table1"

Date(Test_Date) as Date1, it will get data into qlikview

Success

Why?

Thanks in advance!

Reasons is I would like to use the following script on Test_Date field

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Test_Date) as minDate,

max(Test_Date) as maxDate

Resident DE_Planned;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

DE_Calendar:

Load

TempDate AS Test_Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: NewBie on Load, combine SQL statement

martinpohl — Thu, 18 Jun 2015 11:36:30 GMT

If you want to use an aggregation function like max and additional fields (in your example Date2) you need to add a group by Date2.

In that case it makes no sense because for each date you will get the same value as max(date).

You can load max(date) resident yourdata into a table and there you will get one dataset.

Regards

Re: NewBie on Load, combine SQL statement

Fri, 19 Jun 2015 07:03:33 GMT

Seems like I am stuck on the Load, resident part.

I try to follow through some good samples here‌‌ step by step, but on join table already throws error.

llegal combination of prefixes

LEFT JOIN (Employees)

mapSPOUSES:

MAPPING

LOAD

EmpId,

‘Y’ as Spouse

Resident Spouses

Therefore, unable to understand why and how the process work! Is this I am using the personal editions that will block some of the function out, causing I cannot do complex load statements?

topic NewBie on Load, combine SQL statement in QlikView

NewBie on Load, combine SQL statement

Re: NewBie on Load, combine SQL statement

Re: NewBie on Load, combine SQL statement