topic Re: Check if a certain never exist for a specific field in QlikView

Check if a certain never exist for a specific field

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I'm trying to check if an value exist in a column. I have the expression :

Avg(

Aggr(

NetWorkDays(

Min(if(Left(matricule,1)='S', date_action)), Max( if([Type de Contact]<>'P91' or [Type de Contact]<>'P92' or [Type de Contact]<>'P93' or [Type de Contact]<>'P98',date_action))),[Numéro de Contrat]))

I'm calculating the average delay for a "numéro de contrat" between the last action of a "matricule" which begin by S and the last action where the "type de contact" is different of P91, P92 ...

The problem is that I want the numéro de contrat where the type de contact is never to P91,P92 ...

For exemple here I have the right delay between 1 oct and 18 nov (35 days) but it's false because this "numero de contrat" is finalised with the P91 the 18 nov also. I only want the delay if P91,P92 ... never exist for this "Numéro de Contrat". So like that I know if the contract is not finalised and since when.

Thank you in advance

Re: Check if a certain never exist for a specific field

rwunderlich — Thu, 19 May 2016 17:20:48 GMT

Change your max() to use set analysis:

Max({<[Type de Contact] -= {P91,P92,P93,P98}>} [Numéro de Contrat])

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Check if a certain never exist for a specific field

Fri, 20 May 2016 08:35:12 GMT

Hello Rob,

I made something easier :

Avg(

Aggr(

NetWorkDays(

Min(if(Left(matricule,1)='S', date_action)), Date(Now())),[Numéro de Contrat]))

But I only want the "Numéro de Contrat" where P91,P92 ... never appear for this "Numéro de Contract", and I tried the set analysis and it does the same than my previous if condition.

Thank you

Re: Check if a certain never exist for a specific field

rwunderlich — Fri, 20 May 2016 16:06:32 GMT

Can you post a sample qvw?

-Rob

Re: Check if a certain never exist for a specific field

swuehl — Fri, 20 May 2016 16:11:11 GMT

Maybe

Avg( {< [Numéro de Contrat] = e({<[Type de Contact] = {P91,P92,P93,P98}>}) >}

Aggr(

NetWorkDays(

Min(if(Left(matricule,1)='S', date_action)), Date(Now())),[Numéro de Contrat]))

edit: Maybe add the set expression also to the Min() function

edit 2: reference: Excluding values in Set Analysis

Re: Check if a certain never exist for a specific field

Fri, 20 May 2016 16:20:52 GMT

It worked perfectly thank you !

What's the difference between set expression with {} and an if condition ?

Re: Check if a certain never exist for a specific field

swuehl — Fri, 20 May 2016 16:52:26 GMT

Both operate at different levels:

Logical Inference and Aggregations