If col equals Sunday then Sun

[Primus]

I'm working on a IF statement 

Here it's what I have

iif([Day])="D-WK", "Sun"

If the col Day = ])="D-WK",  give me Sun under a new col named Sunday

it creates a seven day look Sun,Mon,Tue,Wed,Thu,Fri,Sat

                                            Sun

[CharlieS]

Hi @Primus 

It seems like you're asking about a day name formatting question ("Sun" to "Sunday") and a data transformation question (Field [Day] to new fields?). Could you provide an visualization to show the Community what you're looking to do? I'm sure it would help get you the response you seek.

[Primus]

The data looks like this

Id         order date  

 abc       D-WK-M

123       D-WK-Tu

I would like  it to look like this

Id         order date          Monday    Tuesday Wednesday   Thursday   friday

 abc       D-WK-M             Mon

123       D-WK-Tu                              Tue

345        D-WK-Fri                                                                                  Fri

[CharlieS]

Thanks for that clarification.

Since "D-Wk-M" isn't actually a date, we've got some string editing to do here. Here's the IF statement you could use to return the day of the week from the "D-Wk-M" values:

IF Right([order date],FindString(ReverseString([order date]),'-'))=="M" THEN "Monday"
ELSEIF Right([order date],FindString(ReverseString([order date]),'-'))=="Tu" THEN "Tuesday"
ELSEIF Right([order date],FindString(ReverseString([order date]),'-'))=="W" THEN "Wednesday"
ELSEIF Right([order date],FindString(ReverseString([order date]),'-'))=="Th" THEN "Thursday"
ELSEIF Right([order date],FindString(ReverseString([order date]),'-'))=="F" THEN "Friday"
ELSEIF Right([order date],FindString(ReverseString([order date]),'-'))=="Sa" THEN "Saturday"
ELSEIF Right([order date],FindString(ReverseString([order date]),'-'))=="Su" THEN "Sunday"
ELSE "Unknown" ENDIF

Expression Breakdown:

We just need the characters after the last '-' to determine the day of the week, yes? Since FindString( only finds the first occurrence, we can use the ReverseString( function so that the last. Knowing how many characters away from the end the last '-' is will let the Right( function return know to return 1 or 2 characters ("M" needs only 1, but "Th" needs 2).

Alternatively, if "D-Wk-" is consistent, you could just include that for a shorter expression:

IF [order date]=="D-Wk-M" THEN "Monday"

and so on. 

So this gives you a new field with the full names as values. If you want to use "Mon" for the true values of a "Monday" field (instead of a 1 or boolean "true"), then those values will also need to be created with another formula (or entered with a table of values. A CrossTab tool can transform the data to the layout you desire. A Select tool will be necessary in this scenario as the CrossTab will arrange the fields in alphabetical order. 

There are some changes I might suggest to this process like using actual date values (like "2020-06-03") so the day of the week can be parsed directly, or using Boolean values instead of "Mon" strings that would probably need to be parsed again by other processes, but I understand if the method above works for your case. 

Check out the attached workflow to see how this can be achieved.

[Primus]

Hi Charles the code worked is there a way to do the same thing but create a new col for each day of the week ? for example after the adding the formula I get this

id      Order Date

123   Mon

134   Fri

342    Unknown

456    Sat

I would like it to look like

id    order date   SUN     MON  TUE  WED THUR   FRI       SAT

123      06/01/20                          MON

134       06/05/20                                                                        FRI

342

456    06/06/20                                                                                  SAT

[CharlieS · Accepted solution]

---

@Primus wrote:

Hi Charles the code worked is there a way to do the same thing but create a new col for each day of the week ? for example after the adding the formula I get this

---

Does "the code" mean the if statement in my reply, or the workflow? In the workflow I attached, the columns are created with a CrossTab tool. I just want to make sure in case the workflow was unclear.