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

[#1]

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;

[Anonymous]

This syntax as rsvebeck suggested always worked for me.  Except I don't use text() when define variable.  Take a look into the log file, see what value was inserted in the place of the variable.

[hcabrera]

Hi,

Try

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

[OmarBenSalem · Accepted solution]

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

[#5]

this worked, thank you!

[RSvebeck]

Its great that you found a solution. Just one advice on this solution. You download 'All this table data' from the SQL server. In your case perhaps it does not matter, but if the source table is very big, this would potentially become heavy for the network and also for your Qlikview server to handle. If possible, find a way to do the limit the data in the SQL query instead, unless the unwanted data is needed in another scenario.

Robert