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
Solved! Go to Solution.
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.
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
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.
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
@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.