Populate New Formula Output Field if date equals to today's date

[arnorian]

I have multiple fields in a file such as product description, product type, price, effective price date, end price date, and currency.

However,  I need to pull only product type, price and effective price date ONLY if the effective date matches today's date. I used the following formulas below but keep getting

'Parse Error at char(24): Malformed If Statement" error

Output Field:     Type:     Expression

ProductType      String    IF([Start Date]=[DateTimeNow(%Y%m%d)],[Product Value],"")

ProductPrice      String    IF([Start Date]=[DateTimeNow(%Y%m%d)],[Price],"")

ProductDate      Date      IF([Start Date]=[DateTimeNow(%Y%m%d)],[Start Date],"")

[NicoleJohnson · Accepted solution]

If you are using an IF statement with commas instead of the words "then" & "else", you need to use IIF (two letter I's)... so:

IIF([Start Date]=[DateTimeNow(%Y%m%d)],[Product Value],"")

Alternatively, if you like to spell things out, you can do the following (just make sure you put the "endif" at the end):

IF ([Start Date]=[DateTimeNow(%Y%m%d)]) Then [Product Value] Else "" Endif

Hope that helps! :)

NJ

[arnorian · Accepted solution]

Nicole,

That kind of worked but now I'm receiving 'Parse Error at char: Unknown variable 'DateTImeNow(%y%m%d).

Isn't DateTimeNow() a native formula that defined within Alteryx and just populates today's date. Which confuses me as to why would it need to be defined as a variable?

[NicoleJohnson]

Ah yes, DateTimeNow should not have anything in the parentheses, and shouldn't have square brackets around it either, sorry for not catching that earlier. Should just use DateTimeNow(), which will put today's date (at the time the workflow is running) in the standard datetime format of yyyy-mm-dd. So to get it to match your [Start Date] field, depending on how that one is formatted, you might need to reformat the start date. Perhaps something like this (use the DateTimeParse tool to convert Start Date to the standard format, by telling it how your field is originally formatted):

* assuming your Start Date field looks like this: "20170613"

IF (DateTimeParse([Start Date],"%Y%m%d")=DateTimeNow()) Then [Product Value] Else "" Endif

This will convert your Start Date to "2017-06-13" and compare it to DateTimeNow (which would also be "2017-06-13" today). Does that help? :)

NJ

[arnorian]

Yes that worked perfectly. 

Thank you

A

[reginawhelan]

Hi, I used your example to format the dates in my flow to be the standard format in my greater than formula:

IF (DateTimeParse([Permanent

Hire Date (YYYY-MM-DD)],"%Y%m%d")>DateTimeNow()) Then "Invalid Date" Else "" Endif

and I receive a DATETIMEPARSE Conv Errors message for all the dates, an example follows:

  Formula (4) DATETIMEPARSE: Cannot convert "1968-12-16" to a date/time with format "%Y%m%d": Expected a number for Month: '-12-16'

Do you know what I could be doing wrong?

Thanks,

Regina