Changing entries within a field

[Ak97]

Hi Everyone

I loaded an excel file and left joined some fields into a script. The common fields are Category and POLICY_TYPE.  the fields that I left joined are Category, POLICY_TYPE and  [Type of Policy]. I also have other fields in the script. I have five types of policies. I am trying to run a script such that:

if([Type of Policy] = 'Super Saver' AND Date(ISSUE_DATE) < 18/06/2020 ,[Type of Policy] = 'Comprehensive',[Type of Policy] = 'Super Saver') as Type_of_Policy

However, when I do this, I get the outputs as -1 and 0 instead of all of the policy types. Why is this happening?

[Dalton_Ruer · Accepted solution]

When you are trying to use an IF like that in the load script, you don't tell it what field to assign the value to, you simply use the value. 

if([Type of Policy] = 'Super Saver' AND Date(ISSUE_DATE) < '18/06/2020' ,'Comprehensive', 'Super Saver') as Type_of_Policy

HOWEVER, if you think logically about your statement you will end up either with Super Saver or Comprehensive as the ONLY 2 Policy Types. 

I think you really want to NEST your IF statements so that your other policy types will come across like below. If the policy type is super saver THEN .... and we will check then if it's < certain date. If it is, then we use Comprehensive, else Super Saver. But If the policy type was NOT super saver we use the final else to use the original policy type:

if([Type of Policy] = 'Super Saver' ,

      if(Date(ISSUE_DATE) < '18/06/2020' ,'Comprehensive', 'Super Saver'),

     [Type of Policy]) as [Type of Policy]