Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator
Creator

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
Highlighted
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
;

 

 

Highlighted
Creator
Creator

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