Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Display a field if another field has more than 1 entry.

Hi Everyone.

I am trying to display the persons name that has been assigned to an order number in a straight table. Some of the names though are blank, so in this case i want to display a Status name instead.

But, some orders have more than one person working on it at different stages, so maybe it is started by one person, but finished by another. In this case i want to display the name of the person who started it.

Does this make sense?

My fields are:

[Operative Name]

[Status Operative]

Stat_StatusID

So i want to do...

If [Operative Name] is null then use [Status Name]. but if Status Name has more than 1 name then use the name assigned to Stat_StatusID  = 2 otherwise use Operative Name.

Can anyone help please?

9 Replies
abhijain
Partner - Contributor III
Partner - Contributor III

Hello ,

Can u tell me what is this [Status Name] is this the Field name

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, sorry! it should be Status Operative.

Thanks

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Can anyone help with this please?

giakoum
Partner - Master II
Partner - Master II

a sample application would help so that we do not have to create data for this.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, i have attached a sample.

The order showing has no name in it (I have done an expression for the logic behind the names) but on this particular one there are multiple names against the StatusID 2 (Done at different times). so i would like to use the latest status name if no other names are available (As per expression)

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

any ideas anyone?

Not applicable

Sounds like you need a nested if statement. try an expression like this.

Distinct should make sure it only counts the names that are truly unique. You may not need it.

=if(isnull([Operative Name]), [Status Operative] , If(Count(Distinct([Status Operative])) >= 1, Status_StatusID=2 , [Operative Name] )  )

swuehl
MVP
MVP

Maybe like this

=if(not isnull([Operative Name]),

     [Operative Name],

     If(Count(Distinct([Status Operative])) > 1,

          FirstSortedValue({<Status_StatusID={2}>} [Status Operative],-Status_ModifyTime),

          [Status Operative])

)

Not applicable

Hi,

You could also try this.

if

(isnull([Operative Name]),

if(count(distinct [Status Operative])>1,

FirstSortedValue({<Stat_StatusID = {2} >} [Status Operative],

[Operative Name])

),
[Operative Name]

)

Regards,

Janzen