New dimension in data load editor with if statement

[heathqm]

I am trying to create a new field in the data load editor using fields that have already been previously loaded in table called Program Info. I keep getting an invalid expression error. I have tried a couple different options that I will list below and both provide an invalid expression error. 

[table1]:

LOAD
[Program UID],
[Program Type],
[Program Acronym],
 if([Program Type] = 'A' OR [Program Type] = 'B' AND
                count([Program Acronym]) <=1 ,
   'Yes', 'No')
 AS [Program Acronym Unique YN]
Resident [Program Info];

I also tried this:

[Table1]:

LOAD
[Program UID],
[Program Type],
[Program Acronym]
Resident [Program Info];

Left Join([Program Info])
Load
[Program UID],
if([Program Type] = 'A' or [Program Type] = 'B' and
                  count([Program Acronym]) <=1,
       'Yes', 'No')
AS [Program Acronym Unique YN]
resident [Table1];

DROP TABLE [Table1];

Any help is greatly appreciated. 

[rbartley]

Hi,

You're using a count in your if statement, but count is an aggregate function and so cannot be used here.  

[heathqm]

How could I change that but still make sure the program acronym is unique? 

[rbartley]

You will need to calculate the [Program Acronym Unique YN] field separately, so something like this:

[table1]:

LOAD
[Program UID],
[Program Type],
[Program Acronym]
Resident [Program Info];

Join

[Program UID],

Count([Program Acronym]) as "Program Acronym Count"

Resident  [Program Info]

Group by [Program UID];

[Table2]:

LOAD
[Program UID],
[Program Type],
[Program Acronym],
 if([Program Type] = 'A' OR [Program Type] = 'B' AND
              "Program Acronym Count" <=1 ,
   'Yes', 'No')
 AS [Program Acronym Unique YN]
Resident [Table2];

Drop Table [Table1];