NewBie on Load, combine SQL statement

[#1]

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; 

[martinpohl]

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

[#3]

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?