Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Frquiroga1995
Contributor II
Contributor II

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

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

Frquiroga1995_3-1606920492587.png

 

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:

Frquiroga1995_4-1606920515424.png

 

 

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 !

Labels (2)
2 Replies
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
Contributor
Contributor

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