If a situation happens show number 1, if it doesn't show a 0

[Frquiroga1995]

Challenging Task

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 !

[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 !

[Kushal_Chawda]

@Frquiroga1995  try below

Data:
load ID,
     Sinister,
     [Policy Number],
     Motivo,
     if(match(Motivo,'Renovacion','Migracion') or len(trim(Motivo))=0,'Matched','Not Matched') as Flag
FROM Table;

Flag:
LOAD ID,
     Concat(DISTINCT '|'&Flag&'|',',') as Flag_values
Resident Data
Group by ID;

Left Join(Data)
LOAD ID,
     if(index(Flag_values,'|Not Matched|'),1,0) as An
Resident Flag;

DROP Table Flag;

[MartinGarrix]

A value is one of the fundamental things — like a letter or a number — that a ... The values we have seen so far are 2 (the result when we added 1 + 1), and ... print 1,000,000 1 0 0 ... This diagram shows the result of the assignment statements: ... In a script, an expression all by itself is a legal statement, but it doesn't do