topic Re: Script to load only data from last month in QlikView

Script to load only data from last month

Wed, 25 Nov 2020 16:16:04 GMT

Forgive me I am new to this and don't know any SQL.

Would someone help me if possible.

I have a load script that connects to an ODBC data source however I only want to retrieve data when (for example) the field opened_at is from last month. The script I'm currently using is shown below however it is taking 20 minute to execute because it's dragging in all records from the file:-

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';

ODBC CONNECT32 TO DataSource\ (UserId is yyyyyyyy, Password is "xxxxxxxxx");

LOAD
Year(opened_at) as OpenedYear,
Month(opened_at) as OpenedMonth,
"dv_assigned_to",
"dv_assignment_group",
"dv_caller_id",
"dv_category",
"dv_closed_by",
"dv_cmdb_ci",
"dv_contact_type",
"dv_escalation",
"dv_impact",
"dv_incident_state",
"dv_opened_by",
"dv_parent",
"dv_priority",
"dv_state",
"dv_u_business_service",
"dv_u_completed_by",
"dv_u_ooh_call",
"dv_u_secondary_business_services",
"dv_u_verify_the_business_service",
number,
"closed_at",
"opened_at",
"reassignment_count";

SQL SELECT active,
"dv_assigned_to",
"dv_assignment_group",
"dv_caller_id",
"dv_category",
"dv_closed_by",
"dv_cmdb_ci",
"dv_contact_type",
"dv_escalation",
"dv_impact",
"dv_incident_state",
"dv_opened_by",
"dv_parent",
"dv_priority",
"dv_state",
"dv_u_business_service",
"dv_u_completed_by",
"dv_u_ooh_call",
"dv_u_secondary_business_services",
"dv_u_verify_the_business_service",
number,
"closed_at",
"opened_at",
"reassignment_count"
FROM "SCHEMA".OAUSER.incident;

Thanks in advance.

Re: Script to load only data from last month

swuehl — Tue, 21 Mar 2017 16:05:50 GMT

Maybe use a WHERE clause with your SQL statement?

The correct syntax to compare your field value against your requested date range might depend on your DBMS, so if in doubt, ask your friendly DBMS /SQL guru nearby.

Re: Script to load only data from last month

vishsaggi — Tue, 21 Mar 2017 16:14:13 GMT

Try this in your where clause: Like

LOAD *;

SQL

SELECT field1,

field2,.....

FROM scheme.oauser.incident

WHERE opened_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AND

opened_at < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

LOAD *

WHERE opened_at >= MonthStart(AddMonths(Today(), -1)) AND

opened_at < MonthStart(Today());

SQL

SELECT field1,

field2,.....

FROM scheme.oauser.incident;

Re: Script to load only data from last month

Tue, 21 Mar 2017 16:52:26 GMT

Thank you both Stefan and Vishwarath I have resolved this with a bit of help from Stefan's comment and a colleague. The revised SQL is below:-

FROM "SCHEMA".OAUSER.incident
WHERE year(opened_at)=year(now())
AND month(opened_at)=month(now())-1
;

All I need to do now is workout how to modify the SQL if the month is January (1)

Thank you both