Need formula for a scenario in Qlik Sense

[vish123]

Hi Team,

I have below scenario. 

As per above scenario , i need to display conflict as a value in column4 when 

column1 = previous (column1) and column2 = previous (column2) and column3 <> previous (column3) then conflict 

In this scenario i need conflict value in both rows that matches above scenario. Can you please help me with formula.

Here is the dummy data :

Table1:

Load * Inline [
Column1 ,Column2, Column3
A, A ,BBC
A, A ,BBC1
C, C, ABC
C, C ,Some
D, D ,Some1
D, D ,Some1

];

Table2:
load *,

if(
Column1 = previous(Column1) and Column2 = previous(Column2) and Column3 <> previous(Column3),
'Conflict','NA') as Column4
resident Table1;

drop table Table1;

[Iswarya_]

Hi Vish123,

Please try as below:

Table1:
Load * Inline [
Column1, Column2, Column3
A, A, BBC
A, A, BBC1
C, C, ABC
C, C, Some
D, D, Some1
D, D, Some1
];

NoConcatenate
Table2:
Load *,
if(Peek(Column1)=Column1 and peek(Column2)=Column2 and peek(Column3)<>Column3,'Conflict','NA') as Column4
Resident Table1
order by Column1,Column2,Column3;

Drop table Table1;

[vish123]

Hi Ishwarya,

I have achieved above output already in Qlik Sense. But what i want is i need "conflict" in both rows where column3 values does not match and all other column values should match. I want output as below.

As shown above highlighted should be "conflict" as well. hope you understand my requirement.

[chris_djih]

1.Make a compined key: column1&'|'column2 as %Key

2. in addtion to the second table where we already have the column4:

Left join(table1)
Load 
   %Key,
   column4
resident table2
where column4='Conflict';
drop table table2;

[vish123 · Accepted solution]

Hi Chris,

Thanks for your reply.

Finally, I have achieved the desired output using below formula.

Aggr(if(
Column1 = above(Column1) or Column1= below(Column1)
and
Column2 = above(Column2) or Column2= below(Column2)
and
Column3 <> above(Column3) and Column3<>below(Column3),
'Conflict','NA'),Column1, Column2, Column3)

Here is the output which i want:

[vish123]

By the way last column is the output