Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
heathqm
Contributor III
Contributor III

New dimension in data load editor with if statement

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. 

3 Replies
rbartley
Specialist
Specialist

Hi,

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

heathqm
Contributor III
Contributor III
Author

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

rbartley
Specialist
Specialist

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