Skip to main content

New to Qlik Sense

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

cancel
Showing results for 
Search instead for 
Did you mean: 
mikael-c
Contributor III
Contributor III

select value in field conditional to other field

I have this table here

mikaelc_0-1643831400844.png

and I want to return the values in the 2nd field ([IQA secteur TR]) based on the 1st character of the 1st field(secteur_période). The first character in the secteur_période will always be the same (1 to 4), everything else changes over time.  Also, this field is a key to other tables.

For example, to get the value 24, i thought that this

=if(left(secteur_période,1) = '1', [IQA secteur TR])

would work, which it does, but also returns a dash to show that other values don't match.

I also thought of trying

=if(left(secteur_période,1) = '1', if(len([IQA secteur TR]) > 0, [IQA secteur TR]))

but it does the same thing.

????????????

mikaelc_1-1643832226802.png

 

1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

Assuming all values follow the format above, you could create an expression like:

Only ({<secteur_période={"1*"}>}[IQA secteur TR])

If there is only one [IQS secteur TR] per secteur_période

View solution in original post

6 Replies
Almen
Creator II
Creator II

I'm not sure what your desired output would be. A table with only IQA secteur TR values? Why is the selection based on period necessary? 

Lisa_P
Employee
Employee

Assuming all values follow the format above, you could create an expression like:

Only ({<secteur_période={"1*"}>}[IQA secteur TR])

If there is only one [IQS secteur TR] per secteur_période

anat
Specialist II
Specialist II

use subfield at script level to create new field then use that field for selection

 

subfield(secteur_période,'-',1) as newfield

expression :only({<newfield={'1'}>}[IQA secteur TR])

 

SubField - script and chart function ‒ Qlik Sense on Windows

 

Vinicius_121
Contributor II
Contributor II

Hello mikael-c,

 

If you want to show the empty lines as blank, not as nulls, you can use "chr(32)" insede if, like that - "if(column1='1', column2,chr(32))", this will show them as blank field,

Vinicius_121_0-1643891774039.png

But if you want to show only the line that match your criteria, there is the option to unmark "include null values"in your dimension.

Vinicius_121_1-1643892011100.png

 

mikael-c
Contributor III
Contributor III
Author

Hi, thanks for your answer. I tried so many variations with single quotes and it would not work. Double quotes did the trick.

¯\_(ツ)_/¯

 

mikael-c
Contributor III
Contributor III
Author

Thanks for your suggestion! However the values are not inserted into a table, they are used for something else where I don't think I can un/check an option to include null values or not. @Lisa_P's suggestion did the trick.