Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the delivery dates for the orders. I need to create a new concept of history. If the code is A, it should be the date of Wednesday of the previous week, and if it is B, it should be 3 days before the delivery date. However, if the 3 days before falls on a Saturday or Sunday, this date must be a Friday.
Try this
Maintable:
Load
OrderID,
DeliveryDate,
If(Code = 'A', Date(WeekStart(DeliveryDate) - 2, 'YYYY-MM-DD'),
If(Weekday(DeliveryDate - 3) <= 5, DeliveryDate - 3, DeliveryDate - Weekday(DeliveryDate - 3) + 5)) as NewDate
Resident YourSourceTable;
If you want previous Wednesday check the where does you weekstart function takes
then use weekstart(Date-7)
if your weekstart takes you to monday add +2 in above expression, Similarly add 3 for sundays etc.
If you only want to return Day, you can create a mapping table to change saturday,Sunday into Friday,
For dates, you can use If(Day(Date)= 'Saturday','Friday',Day(Date)) {Similar for Sunday and make sure Day Format is Same}.
Now Your Question if you want Date:-
If(Code='A',Date(weekstart(Date-7) + 2),
If(Code='B',Date(Delivery_Date-3))) as K:
Next If(Day(K) = 'Saturday',Date(K-1),
If(Day(K)='Sunday',Date(K-2),Date(K)
)) as Required Date;
/////////// You can use Resident or preceding load for required Date///////////////////////
If you want day you can change it to at required date or before.