topic Need formula for a scenario in Qlik Sense in App Development

Need formula for a scenario in Qlik Sense

vish123 — Tue, 22 Feb 2022 10:17:35 GMT

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;

Re: Need formula for a scenario in Qlik Sense

Iswarya_ — Tue, 22 Feb 2022 12:44:44 GMT

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;

Re: Need formula for a scenario in Qlik Sense

vish123 — Wed, 23 Feb 2022 09:26:36 GMT

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.

Re: Need formula for a scenario in Qlik Sense

chris_djih — Wed, 23 Feb 2022 09:39:21 GMT

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;

Re: Need formula for a scenario in Qlik Sense

vish123 — Wed, 23 Feb 2022 12:59:38 GMT

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:

Re: Need formula for a scenario in Qlik Sense

vish123 — Wed, 23 Feb 2022 13:02:00 GMT

By the way last column is the output