If statement error

[JAlex]

Hello Community,

I am trying to add If statements to a load script, but am encountering an error that seems like there is a limit to the number of If statements you can have.

Here is the section of the load statement I am trying to update:

LOAD ProjectID_SYS,
If([Project Model Name]= 'General Product or Process',
If([Current Phase ID]= 1,'Preliminary Investigation',
If([Current Phase ID]= 2,'Business Case',
If([Current Phase ID]= 3,'Product Development',
If([Current Phase ID]= 4,'Process Development',
If([Current Phase ID]= 5,'Launch','Post-Launch'
))))),

If([Project Model Name]= 'Fast Track Stage-Gate',
If([Current Phase ID]= 1,'Product Development',
If([Current Phase ID]= 2,'Process Development',
If([Current Phase ID]= 3,'Launch','Post-Launch'
)))),

If([Project Model Name]= 'Specialty Compounds Stage Gate Model',
If([Current Phase ID]= 1,'Define',
If([Current Phase ID]= 2,'Development',
If([Current Phase ID]= 3,'Validation',
If([Current Phase ID]= 4,'Launch','Post-Launch'
))))))
as [Project Current Stage]
RESIDENT [Project Details];

When I try to add the third Project Model Name If statement, I get an error that states: Error in expression: If takes 2-3 parameters

Any help would be greatly appreciated!

[Vegar]

I don't see why it should not work. Check your commas and parentheses to make sure they are correct.

[JAlex]

Hi Vegar. Yes, I have checked syntax and no errors there. This is error:

[sunny_talwar · Accepted solution]

You can give this a shot

If([Project Model Name]= 'General Product or Process', Pick([Current Phase ID], 'Preliminary Investigation', 'Business Case', 'Product Development', 'Process Development', 'Launch', 'Post-Launch'),

If([Project Model Name]= 'Fast Track Stage-Gate', Pick([Current Phase ID], 'Product Development', 'Process Development', 'Launch', 'Post-Launch'),

If([Project Model Name]= 'Specialty Compounds Stage Gate Model', Pick([Current Phase ID], 'Define', 'Development', 'Validation', 'Launch', 'Post-Launch')))) as [Project Current Stage]

[JAlex]

Hi Sunny - I am not quite sure how you did that, but it appears to be working as expected. I will need to read up on the PICK statement asap . Thank you for your help!

[Vegar]

The solution of @sunny_talwar  will only work for the 'post-launch'  if post-launch got the corresponding [Current Phase ID] per Project Model Name.  

By the way, I found the "error" in your IF. If you still want to use it then you can restructure it like this.

LOAD ProjectID_SYS,
If(  [Project Model Name]= 'General Product or Process',
  If(  [Current Phase ID]= 1,'Preliminary Investigation',
    If(  [Current Phase ID]= 2,'Business Case',
      If(  [Current Phase ID]= 3,'Product Development',
        If(  [Current Phase ID]= 4,'Process Development',
          If(  [Current Phase ID]= 5,'Launch',
            If(  [Project Model Name]= 'Fast Track Stage-Gate',
              If(  [Current Phase ID]= 1,'Product Development',
                If(  [Current Phase ID]= 2,'Process Development',
                  If([Current Phase ID]= 3,'Launch',
                    If([Project Model Name]= 'Specialty Compounds Stage Gate Model',
                      If([Current Phase ID]= 1,'Define',
                        If([Current Phase ID]= 2,'Development',
                          If([Current Phase ID]= 3,'Validation',
                            If([Current Phase ID]= 4,'Launch',
                              'Post-Launch'    
) ) ) ) ) ) ) ) ) ) ) ) ) ) ) as [Project Current Stage]

[sunny_talwar]

Agree with @Vegar , but Vegar there are ways around it rather than writing a huge if statement