select value in field conditional to other field

[mikael-c]

I have this table here

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.

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

[Almen]

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 · Accepted solution]

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]

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]

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,

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.

[mikael-c]

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]

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.