Qlik Community

Ask a Question

New to QlikView

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

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead 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
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,

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

View solution in original post

5 Replies
MVP
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,

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

View solution in original post

Specialist
Specialist

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

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