Ensuring a date filed is on or before the beginning of the current week based on the current date

[Deano478]

Hi Everyone,

This is my first post on the community so I hope I do this right as I know all software communities are different. So i’m essentially making the effort to learn Knime as I have a lot of experience with Alteryx and want to try converting some of my Altyerx flows to Knime.

So my question is I have data ranging from Jan 1 2023 to Today and I have this conditional filter I made in Alteryx like so:
[EntryDate] >= “2024-01-01” AND
[EntryDate] <= ToDate(
DateTimeAdd(DateTimeToday(),
-(ToNumber(
DateTimeFormat(
DateTimeToday(),‘%u’))-1),
‘day’))

What it does is basically ensure that the [EntryDate] is on or before the beginning of the current week (Monday) based on the current date. But for the life of me I cant figure out how to do this in Knime at all.

Any help would be much appreciated just to keep the ball rolling. Thank you in advance

[takbb]

Hi @Deano478 , welcome to the KNIME community.

It is fair to say that the Column Expressions date calculation functions are a little confusing, but to find the first day of the current week, you can use the following expression:

getDayOfWeek() returns 1=Sun, 2=Mon… 7=Sat. It would have been easier if Mon=0 through to Sun=6. So we need to calculate the offset for the current day from our required first day of the week.

For first day of week being Monday:

// need to handle sunday differently to the other days..
offset=getDayOfWeek(today())==1?-6: -1 * (getDayOfWeek(today())-2)

// So...  Sun=-6, Mon=0, Tue=-1, Wed=-2, Thu=-3, Fri=-4, Sat=-5

For first day of week being Sunday:

offset=-1 * (getDayOfWeek(today())-1)
// So...  Sun=0, Mon=-1, Tue=-2, Wed=-3, Thu=-4, Fri=-5, Sat=-6

we can then calculate the first day of the week using this offset:

firstDayOfWeek=plusTemporal(today(),periodOfDays(offset))

Assuming the “Monday” variant, you could then use this kind of construct:

// for Monday as first day of week:
offset=getDayOfWeek(today())==1?-6: -1 * (getDayOfWeek(today())-2)

firstDayOfWeek=plusTemporal(today(),periodOfDays(offset))

if (column("EntryDate") >= "2024-01-01" && column("EntryDate") <= firstDayOfWeek)
{
    // something... if true
    true
}
else
{
    // something... if false
    false
}

EDIT: Have updated the calculation to correctly handle the days of the week! Hopefully it’s correct now. Need more coffee!

[takbb]

Assuming Monday is the first day of the week, there is also a “java hack” that can be used in Column Expressions:

var firstDay=java.time.LocalDate.now().minusDays(java.time.LocalDate.now().getDayOfWeek().getValue() - 1 )
if (column("EntryDate") >= "2024-01-01" && column("EntryDate") <= firstDay)
{
    true
}
else
{ 
   false
}

This code may be considered simpler than the previously mentioned column expressions code!

[Deano478]

@takbb

var firstDay=java.time.LocalDate.now().minusDays(java.time.LocalDate.now().getDayOfWeek().getValue() - 1 )
if (column("EntryDate") >= "2024-01-01" && column("EntryDate") <= firstDay)
{
    true
}
else
{ 
   false
}

Hey @takbb this is some very interesting stuff the transition from Altyerx to Knime is definitely steep but seeing this level of functionality and the wide berth if expressions that can be used I think this is gonna be a fun journey

[takbb]

Hi @Deano478 , thanks for marking the solution!

Yes the transition can appear daunting at times. Both Alteryx and KNIME are good products, with quite a number of similarities, but also quite a few differences.

Each has its own way of doing things, with strengths and weaknesses. I used Alteryx for a couple of years before discovering KNIME. It was great for the data transformations but KNIME is a far better fit for me for the type of work I do, and there are things that I struggled to do with Alteryx which are simple in KNIME, (mostly around working with databases), but I know for some use cases the reverse is true.

There are a (growing!) number of people on the forum here who have at least some experience of Alteryx so if you run into difficulties, hopefully there will be help available.