Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My app's global variables are as follows:
TimeFormat: h:mm:ss TT
DateFormat: M/D/YYYY
TimestampFormat: M/D/YYYY h:mm:ss[.fff] TT
I have a data source (an Excel file) with dates like the following:
9/6/2018 3:50:36 PM
I have another data source (a SQL Server table) with dates like the following:
201-09-06
My SQL table is my date dimension table. So, I want to filter its data to the range of dates from my Excel source. So, I am doing the following:
Temp: Load Date(Min(ExcelDate), 'YYYY-MM-DD') as MinDate ,Date(Max(ExcelDate), 'YYYY-MM-DD') as MaxDate Resident Excel_table ; Let vDateMin = Peek('MinDate', 0, Temp); Let vDateMax = Peek('MaxDate', 0, Temp); Drop Table Temp; dimension_date: Load SQLDate as ExcelDate Where SQLDate >= $(vDateMin) and SQLDate <= $(vDateMax) ; SQL SELECT SQLDate FROM SQL_table ;
However, this is returning 0 rows into the dimension_date table. How can I resolve this?
I figured the solution to my problem. The error is in my dollar-sign expansion. The solution is:
dimension_date: Load SQLDate as ExcelDate Where SQLDate >= Date#('$(vDateMin)', 'YYYY-MM-DD') and SQLDate <= Date#('$(vDateMax)', 'YYYY-MM-DD') ;
Try to use interpretation function when you load MinDate and MaxDate from your Excel file.
Temp: Load Date(Min(Date#(ExcelDate,'DD/MM/YYYY hh:mm:ss TT')), 'YYYY-MM-DD') as MinDate ,Date(Max(Date#(ExcelDate,'DD/MM/YYYY hh:mm:ss TT')), 'YYYY-MM-DD') as MaxDate Resident Excel_table ;
I figured the solution to my problem. The error is in my dollar-sign expansion. The solution is:
dimension_date: Load SQLDate as ExcelDate Where SQLDate >= Date#('$(vDateMin)', 'YYYY-MM-DD') and SQLDate <= Date#('$(vDateMax)', 'YYYY-MM-DD') ;