topic Re: New dimension in data load editor with if statement in App Development

New dimension in data load editor with if statement

heathqm — Mon, 27 Sep 2021 15:20:21 GMT

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.

Re: New dimension in data load editor with if statement

rbartley — Mon, 27 Sep 2021 15:43:55 GMT

Hi,

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

Re: New dimension in data load editor with if statement

heathqm — Mon, 27 Sep 2021 15:50:39 GMT

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

Re: New dimension in data load editor with if statement

rbartley — Mon, 27 Sep 2021 16:00:27 GMT

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];