Challenging Task

[Frquiroga1995]

Hello Everyone !

I am hardly trying to do something, and not very sure how.. Maybe someone can give me some help (I am a newbie at Qlikview)

I have a database like this (It has nearly 30,000 records).

The thing is that one ID, can be associated with many Sinisters and Policy numbers. And for each Policy Number there is a Motivo. (Policy Number as well as Motivo can have Null Values).

I need to create a new field named An that shows..

Number 0: If an ID, has ANY policy number with "Renovacion" , "Migracion" or NULL in Motivo.

Number 1: If there is in Motivo something different than "Renovacion", "Migracion" and there is not a NULL value.

Example: For the first ID: 99878, it has 2 Policies. One with renovacion and the other doesn´t have Motivo... So in An should be a 0.

For the colored ID 345676, it has 4 policies (one is NULL), and has Renovacion as Motivo for one of them. But... for the rest policies it has motivos different than renovacion, migracion or NULL (Like Denuncia or Mal Servicio).. so In An must be a number 1 for all of the records associated with the same ID. I am not very sure If I am being clear.

The result must be like this:

What I did in a first instance was creating an expression in the table like this:

if((Match(Motivo, 'Renovacion','Migracion')), 0,IF(IsNull(Motivo),0,1))

But that works only for each row... It puts a 0 or a 1 depending what it is in Motivo... It doesn't consider the history for each ID.

Thank you very much !

[edwin]

the simplest way is to create a new field that translates nulls into a test value say 'NULL'  this way you dont have to deal with how to write the set analysis for ANDing no nulls

for your expression you use the p function and e function.  see attached