Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Date Adjustment by excluding Holidays.

Hi Team,

I have two file. In One file containing Two Columns Order Date and Request Date.

And in 2nd file contains Holidays.

If the Holiday Date Lies b/w Order Date and Request Date then in that case we need to adjust the Request Date by One.

How we can do it. Please help me on that.

Attached the Data files here.

Thanks

Manish

1 Reply
vishsaggi
Champion III
Champion III

When you say adjust you mean add one day or remove one day?

Based on your excel try below script:

ReqDate:

LOAD [Order Date],

     Request_Date

FROM

[..\Holidays.xls]

(biff, embedded labels, table is Data$);

JOIN(ReqDate)

Holidays:

LOAD Dates AS HolidayDt

FROM

[..\Holidays.xls]

(biff, embedded labels, table is Holiday$);

NoConcatenate

Final:

LOAD *,

     IF(HolidayDt >= [Order Date] AND HolidayDt <= Request_Date, Date(Request_Date+1), Request_Date) AS ReqAdjDate,

     IF(HolidayDt >= [Order Date] AND HolidayDt <= Request_Date, 1, 0) AS ReqAdjDateFlag

Resident ReqDate;

Drop Table ReqDate;