Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
skyline01
Creator
Creator

need help converting a date format in a Where clause

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?

Labels (1)
1 Solution

Accepted Solutions
skyline01
Creator
Creator
Author

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

View solution in original post

2 Replies
sergio0592
Specialist III
Specialist III

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
;

 

 

skyline01
Creator
Creator
Author

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