Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Nested If Then Else Statement based on Date Issues

arnorian
6 - Meteoroid

I have a file that contains a list of all products. However, whenever there's a price change to products the same list gets generated but with new updated prices that overrides the older price. So the effective start date lets me know if the prices have changed. I want to create a workflow that can only pull data where 'effective start date' matches today's date to populate that product's information into the final output.

 

I have tried using formulas shown below but Price shows as '0' and effective date as 'null'

 

Please advise

 

Sample Data:

Price List Name                Product Number    Currency            Price          Start Date     End Date

                                                (string)

US Commercial Products    210-595-2222         USD                   $1000         6/21/2017   12/31/2017 

US Commercial Products    333-595-2221         USD                   $500           1/1/2017     12/31/2017 

US Commercial Products    210-595-2221         USD                   $250           6/21/2017    12/31/2017 

US Commercial Products    333-595-2223         USD                  $1150          11/1/2016    12/31/2017 

 

 

The final file must be in the following format that contains the following fields and following order:

 

Mfg Name (leave blank)      Product ID     Product IDQualifier (Leave blank)     Price      Effective Date

 

Sample Output based on above data:

Mfg Name (leave blank)      Product ID     Product IDQualifier (Leave blank)     Price      Effective Date

                                             210-595-2222                                                               $1000     06/21/2017

                                             210-595-2221                                                               $250       06/21/2017

 

I'm using the following formulas to populate the price and the effective date fields in formula tool:

ProductID: IF [Start Date]=DateTimeNow() Then [Product Value] Else "" Endif

ProductPrice: IF [Start Date]=DateTimeNow() Then [Price] Else "" Endif

EffectiveDate: IF [Start Date]=DateTimeNow() Then [Start Date] Else "" EndIf

 

4 REPLIES 4
NicoleJohnson
ACE Emeritus
ACE Emeritus

First thing I would check is your field types. You'll want to make sure your Start & End date fields are in standard DateTime format. Use the DateTime tool or the DateTimeParse formula to convert those two fields to DateTime type prior to the formulas you have listed below... if that doesn't fix it (or if you need assistance with the date formulas), let us know? Would be helpful at that point if you could attach a copy of your workflow with some dummy data so that we can check for other setup/configuration issues that might be causing issues. :)

 

Hope that helps!

 

Cheers,

NJ

Philip
12 - Quasar

Hi @arnorian

 

The reason the formula doesn't work is because you used DateTimeNow() instead of DateTimeToday(). DateTimeNow() will return today's date along with the time it is generated. The times won't match.

 

Here is how I created the output you wanted. First, I used a Filter Tool to find those that are effective today, then a Formula Tool to create the blank fields.

 

Let me know if you have any questions or additional problems.

 

ProductEffectiveDateFilter.png

Philip
12 - Quasar

And like @NicoleJohnson said, make sure that the start date and end date are Date data types.

rohanonline
10 - Fireball

Try the attached workflow. This should work. Let me know if you still have any requirements.

Labels