Solved: How to create a new field based on the presence or absence of the date in another field

[Marie201931]

I need to create a field of [team formation status]  based on the condition of another field [Project Development Assignments Complete (Date)]. If there is a date then Yes, otherwise No.

Here is my code:

Load *,

If(isnull([Project Development Assignments Complete (Date)]),'No','Yes') as [Team Formation Status]  From (the location)

The problem is it doesn't return to the right value. All of the status is Yes, which is not right because there are about 60% has no team formation date, so it should be a No in the [team formation status] field.

Thank you very much for your help.

It turned out my source data have some issues. The field can have value of a date, YES or NO. So we changed the code to

If(len([Project Development Assignments Complete (Date)])>3,'Yes','No') as [Team Formation Status]  From (the location)

It worked.

Thanks for all your input.

[bharathadde]

Try this 

If(len([Project Development Assignments Complete (Date)])>0,'Yes','No') as [Team Formation Status]  From (the location)

Hope this works for you

[VishalWaghole]

Try

If(len([Project Development Assignments Complete (Date)])<1,'No','Yes') as [Team Formation Status]
From (the location)

Regards,
Vishal

[Marie201931]

Hi Bharathadde, thank you very much. It didn't work.

[Marie201931]

Thanks Vishawaghole. It still doesn't work.

[bharathadde]

Please share you .qvw file if possible

[Marie201931]

It was solved. Nothing to do with the code. My source data contains YES, NO or Date values for the team formation date filed. I changed to code like below, it worked.

If(len([Project Development Assignments Complete (Date)])>3,'Yes','No') as [Team Formation Status] From (the location)