Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Breezy
Creator
Creator

Data load and if/then/else statements

Hi,

I uploaded my Excel document and it has numbers (as text; I'm not doing sums, etc.). Some of the cells have 'Not Available'. I can't figure out how to say "If the population count is 'not available' then put 'not available' in the cell. Otherwise input whatever is in the population count cell in the Excel doc.

Here's what I tried. It did not work though (error during data load script)

If ("PopulationCount" = 'Not Available', 'Not Available', "PopulationCount")

Labels (1)
8 Replies
jwjackso
Specialist III
Specialist III

The ALT  function may work for you.

 

Alt([PopulationCount],'Not Available') as [PopulationCount]

 

Breezy
Creator
Creator
Author

Thank you. I am copying and pasting that syntax but alas, it's not resolving the issue.

jwjackso
Specialist III
Specialist III

Add the Num  function around the [PopulationCount] to convert it to a number for the Alt function

Alt(Num([PopulationCount],'Not Available') as [PopulationCount]

 

Breezy
Creator
Creator
Author

Hmm, the 'as' turned red and the second part of the clause was pinked out (both for 'error').

Breezy_0-1641333648216.png

I added an ')' at the end but it did not do the trick.

Alt(Num([PopulationCount],'Not Available') as [PopulationCount])

jwjackso
Specialist III
Specialist III

I can't type

Alt(Num([PopulationCount]),'Not Available') as [PopulationCount]

 

Breezy
Creator
Creator
Author

Hi,

I still can't get it. Question for you - if I were to change the heading to Pop Count would I need quotes around Pop Count since there's a space?

jwjackso
Specialist III
Specialist III

Yes, you could use double-quotes or [ ] for fields with spaces in the name.

Breezy
Creator
Creator
Author

Well, it loads but it still doesn't reflect it in the table. I must be missing something.