Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cas-ictp
Contributor
Contributor

If statement nested

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

Labels (3)
1 Solution

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder

View solution in original post

8 Replies
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
cas-ictp
Contributor
Contributor
Author

Hi Jordy,

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

Cas

JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
cas-ictp
Contributor
Contributor
Author

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
Master
Master

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.
HirisH
“Aspire to Inspire before we Expire!”
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
cas-ictp
Contributor
Contributor
Author

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
Partner - Master
Partner - Master

Hi Cas,

Great! Please close this ticket and good luck.

Jordy

Climber

Work smarter, not harder