Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akuttler
Contributor

Filtering Dates in Load Script

Hello,

What is the syntax I would use to filter for dates in my data Load?

For example:

PL_ENC:

LOAD

    Date(Floor("SERVICE DATE")) as "Date",

    PATIENTID,

    CPT,

    LocationName,

    RenderingPhysician,

    ITEMTYPE,

    Units as UNITS,

    PracticeName,

    ENCID,

    CPT_CATEGORY

FROM [lib://ENTERPRISE/PATIENT LEDGER.xlsx]

(ooxml, embedded labels, table is [Charge Pivot])

WHERE "SERVICE DATE" BETWEEN '01-01-2017 and  '12-31-2017'


Thanks in advance.

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Filtering Dates in Load Script

May be -

WHERE Date(Floor("SERVICE DATE")) >= MakeDate(2017,01,01) and Date(Floor("SERVICE DATE")) <= MakeDate(2017,12,31)

5 Replies
Digvijay_Singh
Honored Contributor III

Re: Filtering Dates in Load Script

May be -

WHERE Date(Floor("SERVICE DATE")) >= MakeDate(2017,01,01) and Date(Floor("SERVICE DATE")) <= MakeDate(2017,12,31)

akuttler
Contributor

Re: Filtering Dates in Load Script

Sorry the delayed response, it worked.

A_C
New Contributor

Re: Filtering Dates in Load Script

Hey I am getting an error message and not sure why. I am trying to filter out my invoice date to only show 2018 data but I keep getting an error message. Here is my script:

LOAD BRANCH_NAME,
INVOICE_DATE,
NATL_ACCT_NO,
CUST_PO_NO;

[SALES_USAGE_HDR]:
SELECT "BRANCH_NAME",
"INVOICE_DATE",
"NATL_ACCT_NO",
"CUST_PO_NO"
FROM "WHSE"."SALES_USAGE_HDR";
WHERE INVOICE_DATE >= MakeDate(2018,01,01) AND INVOICE_DATE <= MakeDate(2018,12,31);

Re: Filtering Dates in Load Script

Makedate function is qlik function where you have written in sql. So, error should be there. Perhaps you may try this way, make sure the format.

LOAD BRANCH_NAME,
INVOICE_DATE,
NATL_ACCT_NO,
CUST_PO_NO WHERE INVOICE_DATE >= MakeDate(2018,01,01) AND INVOICE_DATE <= MakeDate(2018,12,31);

[SALES_USAGE_HDR]:
SELECT "BRANCH_NAME",
"INVOICE_DATE",
"NATL_ACCT_NO",
"CUST_PO_NO"
FROM "WHSE"."SALES_USAGE_HDR";
Life is so rich, and we need to respect to the life !!!
A_C
New Contributor

Re: Filtering Dates in Load Script

It worked thanks!

Community Browser