Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

load editor datefield <today()-30 days of data question

Hi,

I need help writing two where clauses in my statement. I need to load the last 30 days of data into my app.

The first clause indicating the status of finalizing works, but the second statement with date is not working, the data for capture date is in the format mm/dd/yyyy.

LOAD pod,

    `capture_date`,

    `note_status`,

    `note_count`;

SQL SELECT pod,

    `capture_date`,

    `note_status`,

    `note_count`,

FROM `ema_dw`.`fact_chart_note_status`

Where note_status = 'FINALIZING' and capture_date < today()-30;

1 Solution

Accepted Solutions
omarbensalem
Esteemed Contributor

Re: load editor datefield <today()-30 days of data question

Maybe like this?

LOAD pod,

    `capture_date`,

    `note_status`,

    `note_count`

Where note_status = 'FINALIZING' and capture_date < today()-30;

SQL SELECT pod,

    `capture_date`,

    `note_status`,

    `note_count`,

FROM `ema_dw`.`fact_chart_note_status`;

14 Replies
MVP
MVP

Re: load editor datefield <today()-30 days of data question

Which database are you pulling this data from? Is it Oracle, Access, SQL Server?

Not applicable

Re: load editor datefield <today()-30 days of data question

it's an ODBC connection to SQL server

MVP
MVP

Re: load editor datefield <today()-30 days of data question

May be this

Where note_status = 'FINALIZING' and capture_date < DATEADD(day, -30, CONVERT (date, GETDATE()));

atkinsow
Valued Contributor II

Re: load editor datefield <today()-30 days of data question

It's in the SQL code so I think you'd need to use the SQL syntax

try GETDATE()  instead of today()

rsvebeck
Valued Contributor

Re: load editor datefield <today()-30 days of data question

make a variable first:


let vMaxDate = text(date(today()-30));


then do this:


LOAD pod,

    `capture_date`,

    `note_status`,

    `note_count`;

SQL SELECT pod,

    `capture_date`,

    `note_status`,

    `note_count`,

FROM `ema_dw`.`fact_chart_note_status`

Where note_status = 'FINALIZING' and capture_date < '$(vMaxDate)'

;



Not applicable

Re: load editor datefield <today()-30 days of data question

thank you for your reply, this didn't work though

Not applicable

Re: load editor datefield <today()-30 days of data question

this didn't work, I think the syntax is diff for SQL.

atkinsow
Valued Contributor II

Re: load editor datefield <today()-30 days of data question

Try

WHERE capture_date< DATEADD(day, -30, GETDATE())

rsvebeck
Valued Contributor

Re: load editor datefield <today()-30 days of data question

Are you sure that the field capture_date is datatype date in your SQL and that your date setting is the same date-format as in Qlikview?