Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hopkinsc
Valued Contributor II

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
New Contributor III

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

Hello ,

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

hopkinsc
Valued Contributor II

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

Hi, sorry! it should be Status Operative.

Thanks

hopkinsc
Valued Contributor II

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

Can anyone help with this please?

giakoum
Honored Contributor II

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

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

hopkinsc
Valued Contributor II

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

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
Valued Contributor II

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

any ideas anyone?

Not applicable

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

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] )  )

MVP
MVP

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

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

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

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

Community Browser