Filtering Dates in Load Script

[akuttler]

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.

[Digvijay_Singh · Accepted solution]

May be -

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

[akuttler]

Sorry the delayed response, it worked.

[A_C]

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);

[Anil_Babu_Samineni]

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";

[A_C]

It worked thanks!

[murugesh]

Team,

Can you help me, how to set date filter in below code

Case1:
Select Id,
Case_Type__c,
LastModifiedDate,
CreatedDate,
Origin,
Status,
Subject from Case;

CaseHistory1:
Select Id,
CaseId,
CreatedDate,
Field,
NewValue from CaseHistory;

CaseTemp:
Load
Id,
Case_Type__c,
LastModifiedDate,
Origin,
Status,
CreatedDate as CaseCreatedDate
Resident Case1;

CaseHistoryTemp:
Load
Id as CaseHistoryId,
CaseId as Id,
Field,
NewValue,
CreatedDate as CaseHistoryCreatedDate
Resident CaseHistory1;

TemporaryTable:
Load
Id,
CaseCreatedDate as IntialDate
Resident
CaseTemp;

join (TemporaryTable)

Load
Id,
max(CaseHistoryCreatedDate) as FinalDate
Resident
CaseHistoryTemp
Where
Field = 'Subject'
and Match (NewValue,'URL LINK SENT', 'CSV FILE SENT', 'SELF INSTALLER', 'INSTALLER ACCEPTED')
Group By
Id;

NWDData:
Load
Id,
NetWorkDays(IntialDate, FinalDate) as NWD
Resident
TemporaryTable;
Drop Table TemporaryTable;