I am trying to set up IF/OR/AND statements for a column with numbers for a CSV file but the formula is replacing some numbers under the "actual in-person attendees" column with 50000. I tried all the statements below with IsNull and IsEmpty. What am I doing wrong? Any help would be greatly appreciated.
IF IsNull([Actual in-person attendees]) AND DATETIMEPARSE([End_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), -7, "days") AND !CONTAINS([Program type], "Trade show") THEN "50000" Else [Actual in-person attendees] ENDIF
IF IsEmpty([Actual in-person attendees]) AND DATETIMEPARSE([End_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), -7, "days") AND CONTAINS([Program type], "Alumni") OR CONTAINS([Program type], "Client") OR CONTAINS([Program Type], "Community") OR CONTAINS([Program Type], "Employer") OR CONTAINS([Program type], "Executive") OR CONTAINS([Program Type], "Golf") OR CONTAINS([Program Type], "Internal") OR CONTAINS([Program type], "Talent") THEN "50000" Else [Actual in-person attendees] ENDIF
IF (IsEmpty([Actual in-person attendees]) AND DATETIMEPARSE([End_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), -7, "days") AND [Program type] = "Alumni event" OR [Program Type] = "Client event" OR [Program Type] = "Community Impact" OR [Program Type] = "Employer of Choice" OR [Program Type] = "Executive education (KPMG Learning Team use only)" OR [Program Type] = "Golf event" OR [Program Type] = "Internal meeting" OR [Program Type] = "Talent acquisition") THEN "50000" ELSE [Actual in-person attendees] ENDIF
@Marcegon
It will be much better you can provide your workflow if you can, with sample input and expectd output
Quick look makes me think maybe you should not quote the "5000" since you mentioned it is a number column?
IF IsNull([Actual in-person attendees]) AND DATETIMEPARSE([End_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), -7, "days") AND !CONTAINS([Program type], "Trade show") THEN 50000 Else [Actual in-person attendees] ENDIF
Thank you! I removed the " " and it worked. Now, I have another number column that I need to flag. It will run after the "actual in-person attendees" statement. I have the statement below but it is not doing anything. Would you be able to tell what is wrong?
IF IsNull([Actual virtual attendees]) AND DATETIMEPARSE([End_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), -7, "days") AND [Actual in-person attendees] = 50000 THEN 50000 Else [Actual virtual attendees] ENDIF
@Marcegon
It appears to be grammarly correct for your statement.
Can you provide sample data set with input and output?
This is an example of the input data. The virtual attendees is blank
Start Date End Date Actual # of in-person attendees Actual # of virtual attendees
2021-10-05T12:30:00 2021-10-05T13:30:00 2700
This is an example of the output data
Start_date End_date Actual in-person Attendees Actual virtual attendees
10/5/2021 10/5/2021 13:30 2700
I can exclude datetime but it is giving me a Parse error at char (160) invalid type in operator = =. (Expression # 3). What is wrong?
IF IsNull([Actual virtual attendees]) AND [Actual in-person attendees] = 50000 THEN 50000 Else [Actual virtual attendees] ENDIF