Paxata

[so]

Hi,

I am trying to create 2  new columns in Paxata with name :Pickup date and Lead.

for Pickup date column,   If I add  order date(Date format) with leads Days(integer value ) should result into Pickup date.  Example: Order date: 11/8/2021, Lead Days : 1, results  into pickup date 11/9/2021

Other question

for generating  Lead column, would have conditional formatting like

If leads Days =< 0 count 0,

if leads Days> 1 count 1,  

if leads Days> 2 count 2, 

 if leads Days> 3count 3,

else : count 4

I will appreciate if someone can help in this regard.

Thank you

[akshay]

For both of the questions, using a computed column is the right approach.

For the first question - where you would like to add an integer to a date value, you would have to use the Dateadd function available on the computed columns expression list.

Your function should look something like this - 

Dateadd(@orderofEDIdate@, @LeadTimeDays@, "Days")

DATEADD(DATETIME, INCREMENT, INTERVAL)

• DATETIME is the date you want to start with.
• INCREMENT is the number you provide to be added to the to the DATETIME.
• INTERVAL is the interval (minutes, days, years, etc.) to add by.

  The following is a list of the recognized values for INTERVAL:

  • Years
  • Months
  • Weeks
  • Days
  • Hours
  • Minutes
  • Seconds
  • Millis

For the second question, you would have to use the nested if statement within the computed col expression

IF(CONDITION, TRUE_VALUE, FALSE_VALUE) - for each condition that you have, you can include it as another if in place of the false_value to make it a nested if statement.

I hope this helps! 

[so]

Thanks for response, I tried the same to get the date in pickup date but result is empty.

for second feature conditional statement works for one condition but unable to work on multiple conditions:IF(CONDITION, TRUE_VALUE, FALSE_VALUE)
I will appreciate if you can write a sample here to follow on..

[akshay · Accepted solution]

Look at the screenshots below that show an example of dateadd and nested if statements.

DateAdd:

Nested If:

[so]

Might be the problem is linked to the order date, changed to date format, I clicked on the graph but don't see result

[akshay · Accepted solution]

So a date function is only considered to be in an accurate Paxata date format if the clock symbol occurs as the datatype and the values are automatically indented to the right hand side. In your case the values are not indented on the right hand side. Click change into date under the column operation tab and use our autodetect format to perform the date conversion. After this, we can use the dateadd function.

[so]

Thanks a lot for the solution of first question!

Still need help in second conditional statement

[so]

Thank you!

[so]

I have another problem with the order date column. few dates are missing and I want to add missing  dates rows by  filling other features with 0 value.

[so]

This is the example.

I have another problem with the order date column. few dates are missing and I want to add missing  dates rows by  filling other features with 0 value.