Exclude weekends for calculating data

[arpita]

Hi

Load

SET_ID,

AGE,

BREAK,

makedate(subfield(FileBaseName(), '_', 7), subfield(FileBaseName(), '_', 6), subfield(FileBaseName(), '_', 5)) as Date

FROM
$(file)

I have used the below expression to find the difference in Count(SET_ID) between a day and its previous day. It works fine for Tuesday till Friday but for the Monday's data, its doesnt show the difference because of the falling weekends. Please help

=Count({<Date={'$(=max(Date))'}>}total SET_ID)-Count({<Date={'$(=date(max(Date)-1))'}>}total SET_ID)

[MayilVahanan]

Hi Arpita,

Bring new field weekday from script & try like below

=date(max({<WeekDay-={'Sun','Sat'},Date=>}Date)-1)

[Vegar]

Hi , I believe that @MayilVahanan  solution still will return Sundays values on a Monday. Please correct me if I am wrong.

I would re-use that approach, but use the second parameter of max()  to fetch the correct previous date. 

Count({<Date={'$(=date(max({< [WeekDay]-={"Sat","Sun"}>}Date,2)))'}>}total SET_ID)

[arpita]

Hi Mayil and Vegar,

Both of these doesnt work

[Vegar · Accepted solution]

The count of SET_ID for the latest non-weekend date in your selection.

Count({< Date={'$(=date(max({<Date={"<=$(=max(Date))"},[WeekDay]={"<5"}>}Date,1)))'}>}total SET_ID)

The count of SET_ID for the second latest non-weekend date in your selection.

Count({<Date={'$(=date(max({<Date={"<=$(=max(Date))"},[WeekDay]={"<5"}>}Date,2)))'}>}total SET_ID)

[arpita]

Thanks Vegar....It worked 🙂

CAn you help me with this

I have a calculated field (CCY_SETID) in my load statement. Now I need to have the total count of (CCY_SETID)  in load script.

TLMMO:

LOAD
SET_ID,
IF(Wildmatch(SET_ID,'*GDS*')=1,NULL(),SET_ID) AS CCY_SETID

FROM
$(file)