Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
skyline01
Contributor

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
Contributor

Re: need help converting a date format in a Where clause

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
Valued Contributor II

Re: need help converting a date format in a Where clause

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
Contributor

Re: need help converting a date format in a Where clause

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