topic Re: Limit the Data load in QlikView

Limit the Data load

fawazqutami — Sun, 21 May 2017 09:10:54 GMT

Hello guys,

I'm trying to limit my data load > 2013 ....

IH_BIL_CHARGE_SLIPS:

LOAD *; SQL

SELECT ID IH_BIL_CHS_ID,

TIME,

DATE_DATE,

SELFPAYER_LC,

PAYER1_LC,

SELFPAYER_PLDIS_LC,

PAYER1_PLDIS_LC,

DISCOUNT,

QTY,

IH_POS_POS_ID,

IH_HCP_HCP_ID,

IH_EMR_VST_ID,

FN_STR_MIT_ID,

IH_EMR_PKG_ID,

STATUS CHS_STATUS,

FULFILLED,

IS_PAID,

RFND_ITEM,

PKG_COMP,

PKG_ITEM,

DATE_CREATED CHS_DATE_CREATED,

DATE_MODIFIED CHS_DATE_MODIFIED,

CREATED_BY CHS_CREATED_BY,

FROM QLIK.IH_BIL_CHARGE_SLIPS

WHERE

DATE_DATE > '01/01/2013';

But I've got the below message:

(ErrorSource: Microsoft OLE DB Provider for Oracle, ErrorMsg: One or more errors occurred during processing of command.: SQL SELECT ID IH_BIL_CHS_ID, TIME, DATE_DATE, SELFPAYER_LC, PAYER1_LC, SELFPAYER_PLDIS_LC, PAYER1_PLDIS_LC, DISCOUNT, QTY, IH_POS_POS_ID, IH_HCP_HCP_ID, IH_EMR_VST_ID, FN_STR_MIT_ID, IH_EMR_PKG_ID, STATUS CHS_STATUS, FULFILLED, IS_PAID, RFND_ITEM, PKG_COMP, PKG_ITEM, DATE_CREATED CHS_DATE_CREATED, DATE_MODIFIED CHS_DATE_MODIFIED, CREATED_BY CHS_CREATED_BY, FROM QLIK.IH_BIL_CHARGE_SLIPS WHERE DATE_DATE > '01/01/2013')

any help

Re: Limit the Data load

fawazqutami — Sun, 21 May 2017 11:11:11 GMT

I think I solved it ......

Load * where Year(Date(DATE_DATE)) > '2013;

SQL Select .... From table name;

Re: Limit the Data load

oknotsen — Sun, 21 May 2017 12:56:20 GMT

You are trying to match a date (which is stored as a number) with a piece of text. That will not work.

You will first have to convert that piece of text to an actual date (or the related number).

Re: Limit the Data load

oknotsen — Sun, 21 May 2017 12:57:36 GMT

That quote right before 2013 should not be in there.

Note that you are effectively loading the years 2014 and later where as in your initial script you seem to be trying to load the data of January 2nd 2013 and later.

Re: Limit the Data load

fawazqutami — Sun, 21 May 2017 13:18:39 GMT

Thank you. you are right...

Re: Limit the Data load

immkhan2 — Sun, 21 May 2017 13:22:55 GMT

The standard date format of Oracle is DD-MON-YYYY (if it is not changed in control file of database). Define the format with Qlik date function and you can easily put the where clause.

Re: Limit the Data load

Digvijay_Singh — Sun, 21 May 2017 13:39:06 GMT

Using Where clause in SQL SELECT will be faster than doing it in preceding load as records will be filtered at source. Try something like this if your date comparison is not working -

WHERE DATE_DATE > to_date('01-01-2013','DD-MM-YYYY');

Re: Limit the Data load

fawazqutami — Sun, 21 May 2017 13:44:02 GMT

Thank you Digvijay