How to get a text in one field with conditions on another field with qliksense

[Bintou15]

Hello, 

I would like to get from my entire sheet  all the CRF where customer contains nothing as measure and  display it . Customer is a text field, it may contains VTC, UD ect only text. So here, customer contains nothing -> isnull(customer) = -1, I would like to get the crf associated which is  38312-8402-PR

 

 

So I tried, 
Only({<customer = {val}>} CRF), here val is a variable = -1. I tried putting Only({<customer = {-1}>} CRF) directly but I got an error.

I also tried maxstring with no success.

Do you please know how to do that? which function to use

thanks a lot for your help.

[Mark_Little]

IF(LEN(Customer) = 0, CRF, Customer)

OR

IF(ISNULL(Customer) = -1, CRF, Customer)

[ogster1974]

Try

Count({<Customer = {"=Len(Trim(Customer))=0"}>} CRF)

As a Calculated 

[Bintou15]

Hello Ogster, first thanks for your response. 

I tried it and nothing showed up, I got 0.

I don't want to count the number of CRF where the customer doesn't exist but I would like to get the CRF where the customer doesn't exist.

Here in my example, I want 38312-8402-PR -> which is the crf where the customer doesn't exit

                                                                                          -> and not 2 which is the number of non existing customer

Hope it's clearer. thanks

[ogster1974]

The create a Master Dimension like

If(Len(Trim(Customer))=0, CRF, Null()) As "New CRFs"

Should give you what you need.

[Bintou15]

When I filter on the CRF  38312-8402-PR, your formula shows the CRF. But when there is no filter, so with your solution I have to filter on every CRF to check if there is a non existing customer.

I would like the CRF with unknown customer to show up without having to check on every crf every time.

We are near the solution 👍

Here is sample data attached

[Bintou15]

Hello, thanks for your response. Both your solutions work but only when we filter on the DELIVERYUNIT with no customer. But without filtering on anything, it doesn't show anything.

I would like to filter for example on one Delivery date, and if there is an unknown customer -> get the CRF or else nothing.

I put a sample date.

[Bintou15 · Accepted solution]

Hello guys, I found a solution.

So I wanted to display CRF where customer is unknown.

SO I created a filter: =aggr(if(isnull(Customer)=-1, 'NOK', 'OK'), DELIVERYUNIT) -> for each DELIVERYUNIT (unique number of an engine)  ->

-> I can then click on NOK and then it shows the CRF with unknown customer for each DELIVERYUNIT 

 So thanks a lot for your responses.