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

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

swuehl — Fri, 17 Jun 2016 15:28:11 GMT

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

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

ziadm — Fri, 17 Jun 2016 15:30:12 GMT

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

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

sunny_talwar — Fri, 17 Jun 2016 16:10:31 GMT

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

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

Fri, 17 Jun 2016 18:15:17 GMT

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

Regards,

Parth

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

Fri, 17 Jun 2016 18:16:27 GMT

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

Regards

Parth