Extracting data from 01.01.13 to present date

Mon, 25 Mar 2013 13:02:54 GMT

I am very new to Qlikview, so be gentle.

I am pulling data from an ODBC connection and need to filter the data so i am only extracting records from 01.01.13 onwards (i do not want to see the data prior to 2013).

My colleague did begin this filtering by adding the following:

where

"org_date"

>= '01/01/2013'

This table is now filtering correctly and i am just seeinh records from 01.01.13 onwards.

Now, that makes perfect sense to me. However, i now need to do the same for 2 other fields (start_date) and (end_date ).

Am i correct in understanding it is not as simple as just repeating this, but i need to run a LOAD statement?

Any advice would be grately appreciated.

Thanks.

Re: Extracting data from 01.01.13 to present date

alexandros17 — Mon, 25 Mar 2013 13:05:22 GMT

If you need 3 sets of data each one filtered the easyest thing is running 3 times the select with 3 different where conditions. ...

Re: Extracting data from 01.01.13 to present date

Mon, 25 Mar 2013 13:20:06 GMT

Thank you.

The next issue i have, is where the 'origination date' of a job is before the 01.01.13 but a 'start date' or the 'end date' are on or after the 01.01.13, the data is still pulling through but (im assuming where Qlikview is excluding the origination dates of before 01.01.13) is just leaving a dash.

How would i get round this?

I hope this makes sense.

Re: Extracting data from 01.01.13 to present date

Mon, 25 Mar 2013 13:22:38 GMT

Hi,

Please attach your sample file. And please explain more briefly.

Regards,

Venkat

Re: Extracting data from 01.01.13 to present date

Mon, 25 Mar 2013 13:44:13 GMT

LOAD wo,
    projectid,
    "pm_grp",
    "grp_code",
    assetno,
    "task_id",
    workreqno,
    "pm_idno",
    "route_id",
    "work_area",
    "work_type",
    "work_locn",
    "work_grp",
    originator,
    phone,
    "orig_priority",
    planner,
    "plan_priority",
    status,
    "org_date",
    "org_time",
    "due_date",
    "due_time",
    "exc_date",
    "exc_time",
    "sched_date",
    "sched_time",
    "cmpl_date",
    "cmpl_time",
    "sc_date",
    "sc_time",
    "supv_id",
    approved,
    "flt_code",
    "act_code",
    "critical_ratio",
    "lab_cost",
    "mtl_cost",
    "con_cost",
    "support_rqmt",
    workreq,
    "corr_action",
    "chg_costcenter",
    "crd_costcenter",
    laccount,
    maccount,
    caccount,
    "plant_no",
    "work_class",
    "finance_clo",
    safety,
    budget,
    "fund_src",
    "ent_date",
    "wo_open",
    dispatch,
    "w_opt1",
    "w_opt2",
    "w_opt3",
    "w_opt4",
    "w_opt6",
    "w_opt7",
    "w_opt8",
    "w_opt9",
    "current_location",
    "cause_code",
    "wo_print",
    "on_dispatch_q",
    approver,
    assetlocn,
    "perm_id",
    assetgrpcode,
    "slot_no",
    "check_out",
    "calibration_flag",
    "assigned_to",
    "org_locn",
    dept,
    "capital_project_flag",
    "start_date",
    "start_time",
    "asset_breakdown_flag",
    "finish_date",
    "finish_time",
    assetshortdesc,
    "org_empl_id",
    "delivery_locn",
    "delivery_notes",
    "delivery_req_date",
    "compliance_type",
    "original_wo";
SQL SELECT *
FROM MainSaver11.dbo.wkaw;
store list1 into list1.qvd(qvd);
drop table list1;

List1:
LOAD wo,
     projectid,
     pm_grp,
     grp_code,
     assetno,
     task_id,
     workreqno,
     pm_idno,
     route_id,
     work_area,
     work_type,
     work_locn,
     work_grp,
     upper (originator) as originator,
     phone,
     orig_priority,
     planner,
     plan_priority,
     status,
     date (org_date) as org_date,
     org_time,
     due_date,
     due_time,
     exc_date,
     exc_time,
     sched_date,
     sched_time,
     cmpl_date,
     cmpl_time,
     sc_date,
     sc_time,
     supv_id,
     approved,
     flt_code,
     act_code,
     critical_ratio,
     lab_cost,
     mtl_cost,
     con_cost,
     support_rqmt,
     workreq,
     corr_action,
     chg_costcenter,
     crd_costcenter,
     laccount,
     maccount,
     caccount,
     plant_no,
     work_class,
     finance_clo,
     safety,
     budget,
     fund_src,
     ent_date,
     wo_open,
     dispatch,
     w_opt1,
     w_opt2,
     w_opt3,
     w_opt4,
     w_opt6,
     w_opt7,
     w_opt8,
     w_opt9,
     current_location,
     cause_code,
     wo_print,
     on_dispatch_q,
     approver,
     assetlocn,
     perm_id,
     assetgrpcode,
     slot_no,
     check_out,
     calibration_flag,
     assigned_to,
     org_locn,
     dept,
     capital_project_flag,
     //start_date,
     //start_time,
     asset_breakdown_flag,
     finish_date,
     finish_time,
     assetshortdesc,
     org_empl_id,
     delivery_locn,
     delivery_notes,
     delivery_req_date,
     compliance_type,
     original_wo
FROM
list1.qvd(qvd)
where "org_date" >= '01/01/2013'
and left(wo,1)<>'P'and left(wo,1)<>'T';

list2:
LOAD wo,
    status as status1,
    "start_date",
    "start_time",
    "end_date",
    "end_time",
    //timeinstatus,
    //"plant_no",
    "login_id";
SQL SELECT *
FROM MainSaver11.dbo.wkaws;
store list2 into list2.qvd(qvd);
drop table list2;

Directory;
LOAD wo,
     status1,
     start_date,
     start_time,
     end_date,
     end_time,
     login_id
FROM
list2.qvd (qvd)
where "start_date" >= '01/01/2013'
and "end_date" >= '01/01/2013'
and left(wo,1)<>'P'and left(wo,1)<>'T';

Although Qlikview is extracting orders with the origination date on or after the 01.01.13, now i have included the tables 'start date' and 'end date' this is scewing the information that is being pulled into my report.

For example, it looks though although an order has been placed before the 01.01.13, because the 'start' or 'end date' is on or after the 01.01.13, it is pulling the information into my report but with dashes in the origination date column.

I do not want to see records where the 'origination date' is before 01.01.13, even if the 'start' or 'end date' is on or after 01.01.13.

I really dont know how else to explain it i am afraid. I hope this helps.

topic Re: Extracting data from 01.01.13 to present date in QlikView

Extracting data from 01.01.13 to present date

Re: Extracting data from 01.01.13 to present date

Re: Extracting data from 01.01.13 to present date

Re: Extracting data from 01.01.13 to present date

Re: Extracting data from 01.01.13 to present date