topic Re: Load data from time DATE_STARTED, 2 weeks back. in App Development

Load data from time DATE_STARTED, 2 weeks back.

Loeckli — Fri, 07 Jan 2022 11:59:50 GMT

Hello Everybody

I am still a beginner when it comes to QlikSense. In the app I'm trying to create, I want to load all data older than 2 weeks than the newest time of DATE_STARTED.

What I have tried until now without success.

LOAD

Timestamp([DATE_STARTED] , 'D/M/YYYY') AS [DATE_STARTED]

....

Where DATE_STARTED >= (today()-14);

What I'm missing, or is this approach efficient ?

Thanks for your time and help

Re: Load data from time DATE_STARTED, 2 weeks back.

rubenmarin — Fri, 07 Jan 2022 12:22:43 GMT

Hi, that should load the last 2 weeks of data, first check if the source has any value in DATE_STARTED later than 24-dec.

You can load this data in a document and create a table with DATE_STARTED as dimension and an expression like "If(DATE_STARTED >= (today()-14), 1, 0)" and confirm it sets '1' on the expected dates.

Re: Load data from time DATE_STARTED, 2 weeks back.

Loeckli — Fri, 07 Jan 2022 13:58:33 GMT

Hi
Thanks for your reply

The DATE_STARTED goes back until 2017, so that wont be a problem.

I'm loading all the tabels from a SQL DB and i want to filter just the newest data as proposed.

Now I have something like this :

LOAD

...

DATE_STARTED
If(DATE_STARTED >= (today()-14), 1, 0),

But i still get an error.

Re: Load data from time DATE_STARTED, 2 weeks back.

Loeckli — Fri, 07 Jan 2022 15:22:37 GMT

It was a synthax error, the code works now and it loads the data but not as intended, it loads data with starting point from july 2021. Do you know why ?

Also for what are the 1 and 0 after the today expression ?

Re: Load data from time DATE_STARTED, 2 weeks back.

rubenmarin — Sat, 08 Jan 2022 11:24:42 GMT

Hi, the 1 and 0 it's just to check wich dates returns 'true' (1) so you can check if the condition works as expected. Btw, I proposed this to use on a table in design, using a table chart, not in script.

Note that Today() will work if you apply it on the load sentence, wich can use qlik script funcions. If you use the Tody() on the 'SQL WHERE' it will not work.

With this little info is hard to know why it shows data since july 2021, I will need more script or a sample app to check.

Re: Load data from time DATE_STARTED, 2 weeks back.

Loeckli — Fri, 14 Jan 2022 09:03:42 GMT

Hey 🙂

I don't quite understand this.
Because in the querery you kindly suggested to me. It looks like this.

If(DATE_STARTED >= (today()-14), 1, 0)

For example: this applied for today (14.01.2022 >= 31.12.2021),1,0)

The query now searches for all projects that have been created in this time frame. What does this TRUE refer to? What is also not quite clear to me is why the TRUE is followed by a FALSE in the same parenthesis?

Thank you

Re: Load data from time DATE_STARTED, 2 weeks back.

rubenmarin — Sun, 16 Jan 2022 08:06:01 GMT

Hi, in your initial post you only give info of that where clause, no more script, and that it wasn't what you want. In the text you said you want data older than 2 weeks, but the condition it's for the last 2 weeks.

I just telling you to do a test to load all dates, and in design do a table with date as dimension and add the expression so you can test how it reolves to each date, the ates with '1' will be the ones filtered by the Where clasuse. In this table adjust the expression to flag the dates you want to filter, once you have done it in this table and you have an expression that flags the dates you want, just use the same expression in the Where clause.

Re: Load data from time DATE_STARTED, 2 weeks back.

chris_djih — Wed, 19 Jan 2022 13:00:40 GMT

most of the time the error lays in mismatch of formats.

your code: Where DATE_STARTED >= (today()-14);

when the field DATE_STARTED is in string (or date) format this will always fail, since (Today()-14) will give you a number not a date or a string

if format ist string: Where DATE_STARTED >= '$(Date(today()-14))'
if format is (qlik)date: Where DATE_STARTED >= Date(today()-14

One last thing to keep in mind: your formating in the load (Timestamp([DATE_STARTED] , 'D/M/YYYY') AS [DATE_STARTED]) will happen AFTER the Where condition. So have to check the raw-format of the DATE_STARTED field. So May try load without changig format to see what format Qlik is reading it in