Straight Table Calculated Dimension with Set Analysis non-numeric values

[joey_lutes]

I have many use cases of trying to limit data in a straight table inside Qlik Sense.  One of the more common one is to only show rows of data where a value is blank in a different column.

IE:

Show me a list of 'Products' where 'Customer' field is blank.
I'm trying to do this with Set Analysis (vs 'IF').  What I THINK should work is:

=aggr(only({$<Products= {"=len(trim(Customer))=0"}> Products}, Products)

Unfortunately, it gives me "Invalid Dimension"

Similar use case would be:
Show me a list of 'Products' where 'Customer' = 'ABC'.

=aggr(only({$<Customer= {"ABC"}> Products}, Products)

I'm assuming syntax is the culprit.  Exhaustive searching has led me here.  Thoughts?

[Taoufiq_Zarra]

Hi,

First in the syntax there is a typing error I think before "Products" and comma you have to close the parenthesis of only.
the same in the second script.

And then in the dimension why not just use the dimension calculate :

=if(Len(Trim([Customer])) = 0,Products, Null())

And for the second one

=if(Customer = 'ABC',Products, Null())

Cheers,

[sunny_talwar · Accepted solution]

I think you need to replace one of the } to ) and also, you have a missing } after }>

=aggr(only({$<Products= {"=len(trim(Customer))=0"}> Products}, Products)

Try this

=Aggr(
    Only({$<Products = {"=Len(Trim(Customer)) = 0"}>} Products)
, Products)

[joey_lutes]

Thanks Sunny.  I think that is correct.  It didn't give me an error this time.

However, I realized, my data model is such that I'm going to have to go at it a different way.    'Customer' (in this example) is not in the same table - and is linked several tables away.  

I appreciate the response as always!

[zied_ahmed1]

Hello,

This one is more simple for you : 

=aggr(if (Len(Trim( Customer))=0,Products,Null()),Products)

Don't forget to not include null values for Products 

Dimension

Best regards