Appropriate value of the field based on a logic in straight table

[#1]

Hello all,

I am using a straight table in Qlikview. I would like to show only one contact per account based on a logic. There is one-to-many relationship between Account and Contact.

Please see below for the fields I would like to show in my table:

Account Name, Account Type, Contact Name, Contact Type

If primary contact (Contact Type) is available, than I would like to show the primary contact.

If primary contact is not available than I would like to show secondary contact.

If both primary and secondary contacts are not available, than any contact from that account works.

Please let me know how to achieve this. Any help is appreciated.

Thank you,

Parth Shah

[swuehl · Accepted solution]

Create a numeric representation of your Contact Type values, either using Dual() function or by creating another field with a 1:1 relation between Contact Type and the new field,

LOAD [Account Name],

          [Contact Name],

          Dual([Contact Type], -Match([Contact Type],'Secondary','Primary') ) as [Contact Type],

...

Then create a chart with dimensions Account Name and Account Type and as expressions

=FirstSortedValue(DISTINCT [Contact Name], [Contact Type])

=FirstSortedValue(DISTINCT [Contact Type], [Contact Type])

[ziadm]

Hi

having your Account number as your Dimension and the expression

if(not isnull([Primary Contact] ),[Primary Contact] ,if (not IsNull([Secondary Contact]),[Secondary Contact],'No Contact'))

[sunny_talwar]

Is this needed in the front end of the application or the back end?

You might be able to use MaxString([Contact Name]) and MaxString([Contact Type]) in the front end of the application to pick one of them.

Dimensions:

Account Name

Account Type

Expressions:

MaxString([Contact Name])

MaxString([Contact Type])

[#5]

Thank you Swuehl. This worked perfect. Have a good one!!

Regards,

Parth

[#6]

Thank you Ziad and Sunny. Have a good one!!

Regards

Parth