Multiple "if(WeekDay(Today())" statements

[goro2010]

Good Day,

I have written a script, If I just use one weekday in the script, lets say like today = Fri, then I get the correct results as per script below:

if(VisitDay = 'Monday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if(VisitDay = 'Tuesday' ,if(WeekDay(Today())='Fri', date(today()+4)),

      if(VisitDay = 'Wednesday' ,if(WeekDay(Today())='Fri', date(today()+5)),

      if(VisitDay = 'Thursday' ,if(WeekDay(Today())='Fri', date(today()+6)),

      if(VisitDay = 'Friday' ,if(WeekDay(Today())='Fri', date(today())),

      if(VisitDay = 'Saturday' ,if(WeekDay(Today())='Fri', date(today()+1)))))))) as CallingDay,

      if(VisitDay = 'Monday' ,if(WeekDay(Today())='Fri', date(today()+4)),

      if(VisitDay = 'Tuesday' ,if(WeekDay(Today())='Fri', date(today()+5)),

      if(VisitDay = 'Wednesday' ,if(WeekDay(Today())='Fri', date(today()+6)),

      if(VisitDay = 'Thursday' ,if(WeekDay(Today())='Fri', date(today()+7)),

      if(VisitDay = 'Friday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if(VisitDay = 'Saturday' ,if(WeekDay(Today())='Fri', date(today()+3)))))))) as Deliveryday

Proof:

But when I have my full weekdays as per my script below, then no values is being populated?? - What could be the reason?

if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Wed', date(today()+5)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Wed', date(today()+6)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Wed', date(today())),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Wed', date(today()+1)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Wed', date(today()+2)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Wed', date(today()+3)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Mon', date(today())),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Mon', date(today()+1)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Mon', date(today()+2)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Mon', date(today()+3)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Mon', date(today()+4)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Mon', date(today()+5)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Tue', date(today()+6)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Tue', date(today())),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Tue', date(today()+1)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Tue', date(today()+2)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Tue', date(today()+3)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Tue', date(today()+4)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Thu', date(today()+4)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Thu', date(today()+5)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Thu', date(today()+6)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Thu', date(today())),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Thu', date(today()+1)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Thu', date(today()+2)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Fri', date(today()+4)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Fri', date(today()+5)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Fri', date(today()+6)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Fri', date(today())),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Fri', date(today()+1)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Sat', date(today()+2)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Sat', date(today()+3)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Sat', date(today()+4)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Sat', date(today()+5)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Sat', date(today()+6)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Sat', date(today()+6)))))))))))))))))))))))))))))))))))))) as callDate,

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Wed', date(today()+6)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Wed', date(today()+7)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Wed', date(today()+1)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Wed', date(today()+2)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Wed', date(today()+5)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Wed', date(today()+5)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Mon', date(today()+1)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Mon', date(today()+2)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Mon', date(today()+3)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Mon', date(today()+4)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Mon', date(today()+7)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Mon', date(today()+7)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Tue', date(today()+7)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Tue', date(today()+1)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Tue', date(today()+2)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Tue', date(today()+3)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Tue', date(today()+6)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Tue', date(today()+6)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Thu', date(today()+5)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Thu', date(today()+6)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Thu', date(today()+7)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Thu', date(today()+1)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Thu', date(today()+4)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Thu', date(today()+4)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Fri', date(today()+4)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Fri', date(today()+5)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Fri', date(today()+6)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Fri', date(today()+7)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if([Calling Day] = 'Monday' ,if(WeekDay(Today())='Sat', date(today()+3)),

      if([Calling Day] = 'Tuesday' ,if(WeekDay(Today())='Sat', date(today()+4)),

      if([Calling Day] = 'Wednesday' ,if(WeekDay(Today())='Sat', date(today()+5)),

      if([Calling Day] = 'Thursday' ,if(WeekDay(Today())='Sat', date(today()+6)),

      if([Calling Day] = 'Friday' ,if(WeekDay(Today())='Sat', date(today()+9)),

      if([Calling Day] = 'Saturday' ,if(WeekDay(Today())='Sat', date(today()+9)))))))))))))))))))))))))))))))))))))) as deliveryDate

Proof:

Thank You!!

[swuehl]

Maybe simplify your date calculation like

//Just create a table with some combinations for testing

INPUT:

LOAD Weekday(Recno()-1) as Visitday

AutoGenerate 7;

JOIN

LOAD Date(Today()+recno()-1) as Today

AutoGenerate 7;

// Calculate the CallingDate

LOAD *,

  Date(Today + Mod( Visitday+7-Weekday(Today),7)) as CallingDate

RESIDENT INPUT;

DROP TABLE INPUT;

Visitday	Today	CallingDate
Mo	12.05.2017	15.05.2017
Mo	13.05.2017	15.05.2017
Mo	14.05.2017	15.05.2017
Mo	15.05.2017	15.05.2017
Mo	16.05.2017	22.05.2017
Mo	17.05.2017	22.05.2017
Mo	18.05.2017	22.05.2017
Di	12.05.2017	16.05.2017
Di	13.05.2017	16.05.2017
Di	14.05.2017	16.05.2017
Di	15.05.2017	16.05.2017
Di	16.05.2017	16.05.2017
Di	17.05.2017	23.05.2017
Di	18.05.2017	23.05.2017

[prieper]

Hi,

would make a kind of a mapping table with the concatenated Weekday for Weekday1 and Weekday2 along with the Days to be added.

(aircode)

mapVisit:

LOAD * INLINE [Combo, Day2Add

15, 3

25, 4

35, 5

....];

and then

TODAY() + APPLYMAP('mapVisit', NUM(WEEKDAY(VisitDay)) & NUM(WEEKDAY(TODAY())))  AS .....

Peter

[swuehl · Accepted solution]

The reason why your nested if() statements don't work as intented:

For a calling day = Monday, you'll step into the THEN branch of your first if statement, but if Today() does not return 'Wed', there is no alternative ELSE branch in the second if() statement, thus the complete statement will return NULL for this record.

Your alternative if() statements for Monday will never be checked.

You would need to restructure your if() statements to make it work, but as suggested above by Peter and me, there are better alternatives.

[goro2010]

Good Day Stefan,

Based upon your above comment, I have made these changes and it is working brilliant!!

IF(WeekDay(Today())='Mon') then

"Mon":

LOAD *,

     VisitDay,

      if(VisitDay = 'Monday' ,if(WeekDay(Today())='Mon', date(today())),

      if(VisitDay = 'Tuesday' ,if(WeekDay(Today())='Mon', date(today()+1)),

      if(VisitDay = 'Wednesday' ,if(WeekDay(Today())='Mon', date(today()+2)),

      if(VisitDay = 'Thursday' ,if(WeekDay(Today())='Mon', date(today()+3)),

      if(VisitDay = 'Friday' ,if(WeekDay(Today())='Mon', date(today()+4)),

      if(VisitDay = 'Saturday' ,if(WeekDay(Today())='Mon', date(today()+5)))))))) as CallingDay,

      if(VisitDay = 'Monday' ,if(WeekDay(Today())='Mon', date(today()+1)),

      if(VisitDay = 'Tuesday' ,if(WeekDay(Today())='Mon', date(today()+2)),

      if(VisitDay = 'Wednesday' ,if(WeekDay(Today())='Mon', date(today()+3)),

      if(VisitDay = 'Thursday' ,if(WeekDay(Today())='Mon', date(today()+4)),

      if(VisitDay = 'Friday' ,if(WeekDay(Today())='Mon', date(today()+7)),

      if(VisitDay = 'Saturday' ,if(WeekDay(Today())='Mon', date(today()+7)))))))) as Deliveryday

FROM

C:\QVD\Info.qvd

(qvd)

;

ELSEIF(WeekDay(Today())='Fri') then

"Fri":

LOAD *,

     VisitDay,

      if(VisitDay = 'Monday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if(VisitDay = 'Tuesday' ,if(WeekDay(Today())='Fri', date(today()+4)),

      if(VisitDay = 'Wednesday' ,if(WeekDay(Today())='Fri', date(today()+5)),

      if(VisitDay = 'Thursday' ,if(WeekDay(Today())='Fri', date(today()+6)),

      if(VisitDay = 'Friday' ,if(WeekDay(Today())='Fri', date(today())),

      if(VisitDay = 'Saturday' ,if(WeekDay(Today())='Fri', date(today()+1)))))))) as CallingDay,

      if(VisitDay = 'Monday' ,if(WeekDay(Today())='Fri', date(today()+4)),

      if(VisitDay = 'Tuesday' ,if(WeekDay(Today())='Fri', date(today()+5)),

      if(VisitDay = 'Wednesday' ,if(WeekDay(Today())='Fri', date(today()+6)),

      if(VisitDay = 'Thursday' ,if(WeekDay(Today())='Fri', date(today()+7)),

      if(VisitDay = 'Friday' ,if(WeekDay(Today())='Fri', date(today()+3)),

      if(VisitDay = 'Saturday' ,if(WeekDay(Today())='Fri', date(today()+3)))))))) as Deliveryday

FROM

C:\QVD\Info.qvd

(qvd)

;

END IF

[swuehl]

If you want to do this this way, you don't need the additional checks for today's weekday in each LOAD, since the check is done outside the LOAD statements.