New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Not applicable

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

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

1 Solution

Accepted Solutions
MVP

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,

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

5 Replies
MVP

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,

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

Specialist

Hi

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

MVP

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

Not applicable

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

Regards,

Parth

Not applicable

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

Regards

Parth