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: