Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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:
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;
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.
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;
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:
By the way last column is the output