Display common rows of 2 fields

[asinghal0412]

Hey Qlik Community,

I have 3 fields: Server, Last login, Email.

In 'server'  value is {JP, IT}

In 'last login' value is {Never}

I want to create a measure in a table chart object (using set analysis) in which I display the common 'Email' of records

whose ('last login' is 'Never' and 'Server' is 'JP') and

whose ('last login' is 'Never' and 'Server' is 'IT'.)

If the question is unclear please let me know.

Thanks,

AS

[MayilVahanan]

HI

Might be, try like below in set analysis

{<LastLogn={'Never'}, Server={'JP'}>*<LastLogn={'Never'}, Server={'IT'}>}

* is intersection
+ is for addition

Depends on ur requirement, you can change it.

[asinghal0412]

thanks, Mayil, can you write the whole expression?

[MayilVahanan]

HI 

Try like below

Count{<LastLogn={'Never'}, Server={'JP'}>*<LastLogn={'Never'}, Server={'IT'}>} Distinct Email)

[asinghal0412]

Thanks Mayil,

But the above code will return the Count of the common 'email' rows, but I want the list of the common 'email'.

Can you alter the code for that?

AS

[MayilVahanan · Accepted solution]

HI,

Where u want to display the email list?
If its KPI, try like this

Concat({<LastLogn={'Never'}, Server={'JP'}>*<LastLogn={'Never'}, Server={'IT'}>} Distinct Email, ',')

Filter:

Aggr(Only({<LastLogn={'Never'}, Server={'JP'}>*<LastLogn={'Never'}, Server={'IT'}>} Distinct Email), Email)

Straight table:

Dim: Email

Exp: Count({<LastLogn={'Never'}, Server={'JP'}>*<LastLogn={'Never'}, Server={'IT'}>} Distinct Email)

[asinghal0412]

Thanks, Mayil, it solves the issue! Have a great day!

Akash

[MayilVahanan]

Happy to know that, finally ur issue resolved 😀