Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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`;
Which database are you pulling this data from? Is it Oracle, Access, SQL Server?
it's an ODBC connection to SQL server
May be this
Where note_status = 'FINALIZING' and capture_date < DATEADD(day, -30, CONVERT (date, GETDATE()));
It's in the SQL code so I think you'd need to use the SQL syntax
try GETDATE() instead of today()
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)'
;
thank you for your reply, this didn't work though
this didn't work, I think the syntax is diff for SQL.
Try
WHERE capture_date< DATEADD(day, -30, GETDATE())
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?