Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

View solution in original post

14 Replies
sunny_talwar

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

Not applicable
Author

it's an ODBC connection to SQL server

sunny_talwar

May be this

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

Anonymous
Not applicable
Author

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

try GETDATE()  instead of today()

RSvebeck
Specialist
Specialist

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)'

;



Svebeck Consulting AB
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

Try

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

RSvebeck
Specialist
Specialist

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?


Svebeck Consulting AB