Last business date

[stonecold111]

Hi qlikss

i have 2 dates : date, business date

this is separate qvd with 2 dates   

date               business date

5/8/2021.      5/7/2021

5/18/2021.    5/18/2021

i have filter in my app that uses today’s date as overall filter on date field, but it has to pick up business date when todays date is not business date.  Suggest me.

in my app

Where date= date(today ());

[MayilVahanan]

Hi @stonecold111 

Do you want to load the maximum date available in the business date info?  If so, try like below

Load Max(BusinessDate) as MaxBusinessDate from yourqvd(qvd);

Let vMaxbusinessDate = Peek('MaxBusinessDate');

Load * from yourqvd(qvd) where Floor(BusinessDate) = $(vMaxbusinessDate);

[stonecold111]

No.  

Imagine i have an app in that i have only single table

load  date, a, b,c,d

from xyz;

where date = date( today ());

If today is holiday it has to look for previous business day 

Now i have a qvd with date business date

date               business date

5/8/2021.      5/7/2021

5/18/2021.    5/18/2021

How can automate this?

[MayilVahanan]

HI @stonecold111 

Might be try like below

SET DateFormat='M/D/YYYY';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

T:
Load * Inline
[
date,business date
5/7/2021,5/7/2021
5/8/2021,5/7/2021
];

Load Max([business date]) as MaxBusinesDay Resident T;
Let vDate = Date(If(not Wildmatch(WeekDay(Today()), 'Sun', 'Sat'), Today(), Peek('MaxBusinesDay')));

NoConcatenate
Load * Resident T where date = '$(vDate)';

DROP Table T;

Hope this is ur requirement:

If Today is week end then vDate variable has value of maximum business day else, it store the Today date info.

You can change the inline table with ur source table.