If statement nested

[cas-ictp]

Hello,

I am working on a Qlik Sense project and want to make a new column(field) derived from another column(field). The source file is a .csv. The id.location column is numerical.

If a value is lower then or equal to 12 classification should be A. 13 and equal to 25, classification B. The remaining should be classification C. After searching the forum I came up with the piece of code:

If([id.location]<=12, 'A', 
	If([id.location]>=13 and [id.location]<=25, 'B', 'C'))

I get an error; ] is missing. I searched, but cannot find a solution. Can someone help me? Thanks.

Regards,

Cas

[JordyWegman · Accepted solution]

Hi Cas,

Are you doing this in the script? Because if you do this, you need to give the statement a 'name'.

Check the following code with your formula:

Test:
Load
	*,
	If([id.location]<=12, 'A', 
	If([id.location]>=13 and [id.location]<=25, 'B', 'C')) as Identifier
;
Load * Inline [
id.location
2
3
6
7
12
87
45
34
23
6
15
17
21
]
;
exit script;

This works fine I only gave it a name, Identifier.

Jordy

Climber

[cas-ictp]

Hi Jordy,

Thank you! I am working with the data manager. Is there a way of doing it there?

Cas

[JordyWegman]

Hi Cas,

Good thing, I didn't know this my self, but your AND function is not working. My advice would be to make this in the script editor. Do you know how to do this?

Jordy

Climber

[cas-ictp]

No, I unfortunately do not know how to do this.  I tried to apply your first comment in the data load script, without success. I am fairly new with Qlik Sense.

[HirisH_V7]

The above statement should work, as you need to create a new column into your table.
In QlikSense you can do the same in Data Load Editor, where as data manager will display table and its associations if any.

[JordyWegman]

Hi Cas,

If you really need this if statement, then we have to give it a go I guess. Can you share your script from the data load editor? I can make an edit and send you this. 

Jordy

Climber

[cas-ictp]

Hi Jordy,

Thank you for your help. I managed to pull it of in data load editor myself.

if ((Trim(left(Trim(mid(Trim(mid([location], 2 + 1)), 1 + 1)), 2))) <= 12, 'A', if((Trim(left(Trim(mid(Trim(mid([location], 2 + 1)), 1 + 1)), 2)))>=13 and (Trim(left(Trim(mid(Trim(mid([location], 2 + 1)), 1 + 1)), 2)))<=25, 'B', 'C')) AS [locationABC]

I copied and reused the simple if statement with only A and B which I generated in Data Manager and adjusted it to my needs.

Regards,

Cas

[JordyWegman]

Hi Cas,

Great! Please close this ticket and good luck.

Jordy

Climber