Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

visualssinziana
New Contributor III

Use variable to filter

I want to use only blank values from a field to filter the other data. But because I don't have values for those cells to select them, I created an If statement to insert value 1 on blank fields:

=if (isnull([asp_date_end_FRS]),1,asp_date_end_FRS)

If I use this expression I receive want I need and is ok (see below second field). I created a variable with this expression because I want to use it to filter other data (charts, tables) (see below the first field).

The issue is when I use the variable with the same expression as above I receive only the value "1" for all fields. I know I'm doing something wrong and I would like to know what

variable filter.jpg

Tags (1)
1 Solution

Accepted Solutions
omarbensalem
Esteemed Contributor

Re: Use variable to filter

I'd try to do it in the sccript:

example, I have a field dim1 where I have some null values:

Table:

Load *, dim1 as YourNewDim from source where dim1<>'' or dim1<> '-'  or len(trim(dim1))> 0 //dpends on how ur null values are


concatenante

load * ,'1' as YourNewDim from source where dim1='' or dim1= '-' or IsNull(dim1) or len(trim(dim1))= 0 ;

and use YourNewDim in your analysis !

Hope this helps

3 Replies
omarbensalem
Esteemed Contributor

Re: Use variable to filter

Why variable? Variable only retain one value at time.

Create a new dimension (a master item) as you've done and filter by it

visualssinziana
New Contributor III

Re: Use variable to filter

I want to use the result of the calculated field as part of a second expression and from I know I can't use a master dimension on a expression. For example:

SUM(if (isnull([asp_date_end_FRS]),1,asp_date_end_FRS)Value),   I just want to add values if the cell on "asp_date_end_FRS" field is blank. Maybe you can point to another solution to select only blanks and add other fields based on this selection?

omarbensalem
Esteemed Contributor

Re: Use variable to filter

I'd try to do it in the sccript:

example, I have a field dim1 where I have some null values:

Table:

Load *, dim1 as YourNewDim from source where dim1<>'' or dim1<> '-'  or len(trim(dim1))> 0 //dpends on how ur null values are


concatenante

load * ,'1' as YourNewDim from source where dim1='' or dim1= '-' or IsNull(dim1) or len(trim(dim1))= 0 ;

and use YourNewDim in your analysis !

Hope this helps