Skip to main content
Announcements
Qlik Launches Open Lakehouse and advanced agentic AI experience in Qlik Answers! | LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ngoel
Contributor II

Use only workday dates in variable

Hello,

 

Is there a way we can only allow a variable to take weekday dates into it as a variable. What I am trying to do is basically below:

Assuming today is monday

Yesterday = Friday's date

Day before = Thursday's date

 

and then use these 2 variables, yesterday and day before into an SQL query in data load editor.

 

Appreciate the help!

 

Thanks,

Naman

Labels (2)
7 Replies
ngoel
Contributor II
Author

I don't understand how to use it from the help page. Is there an example where it is being passed as a variable in a query? The examples on the help page are creating there own table and then passing it as a formula in the table. I dont think I am able to translate that to my use case.

marcus_sommer

For example something in this way:

let var = date(lastworkdate(today(), 1), 'YourSQLFormat');

and then:

sql select * from Source where DateField = '$(var)';

ngoel
Contributor II
Author

thank you so much!

Just once last question, and please pardon me if this is non sensible, I am very new to the tool. When you say 'YourSQLFormat' what do you mean here? Like the type of SQL tool I am using to get my data? 

 

Appreciate the help!

marcus_sommer

The data-type/format of the values within the data-base might be different to default-format from Qlik (defined with the interpretation-variables respectively without them are the OS region settings taken).

It depends on the data-base which syntax is accepted as valid - whereby you may also apply a converting on the sql-side, like:

et var = floor(lastworkdate(today(), 1);

and then:

sql select * from Source where cast(DateField AS int) = $(var);

The correct syntax depends on your data-base.

ngoel
Contributor II
Author

I am probably doing something wrong, but even after using "yesterday = floor(lastworkdate(today(), 1);" my yesterday is still falling to a sunday whenever I run this on a monday, instead of falling to a friday

marcus_sommer

It's from your snippet not clear how the variable is created and then called. Beside this you could simply test the date-logic by applying the lastworkdate() within a text-box in the UI - if it worked like expected it could be transferred to the script and then it's just a matter of syntax.