topic need help with the expression in QlikView

need help with the expression

RheaQv12 — Wed, 13 Nov 2019 18:27:03 GMT

Guys,

I have a daily .csv file which i load into QVD - its like users of an application and i am trying to derive or calculate the following expression...but its not giving me the right result

AGGR(count( if(Scope='In Contract' OR Scope='Not in Contract', Email),Date,Name)

I need to find the persons with 2 different emails for the 'In Contract' and 'Not in Contract' scope for each day for each person ... for the person or user when the scope is 'In Contract' and 'No In Contract' then only look for that persons emails and if they are different then count for that day.... attached sample file

thanks a lot

Re: need help with the expression

sunny_talwar — Wed, 13 Nov 2019 18:30:46 GMT

Based on the sample attached, what is the count you expect to see and in which object?

Re: need help with the expression

RheaQv12 — Wed, 13 Nov 2019 19:00:02 GMT

Hey Sunny, thank you. I think my expression wit the if condition is wrong.. the expression should only look for the users when they have 'In Contract'

i am looking for the straight table -

Re: need help with the expression

RheaQv12 — Wed, 13 Nov 2019 19:01:13 GMT

Sunny, thanks a lot

Re: need help with the expression

RheaQv12 — Wed, 13 Nov 2019 19:03:21 GMT

Sunny,

Please ignore the message with "he expression should only look for the users when they have 'In Contract' "

This should been "The expression should only look for the users when they have "In Contract" and "Not In Contract"

Re: need help with the expression

sunny_talwar — Wed, 13 Nov 2019 19:22:01 GMT

Why did you not include Michael Mose? He seems to have two different ids?

=Count(DISTINCT {<Name = {"=Count(DISTINCT {<Scope = {'In Contract', 'Not in Contract'}>} Email) > 1"}>} Email)

Re: need help with the expression

RheaQv12 — Thu, 14 Nov 2019 01:50:45 GMT

Hey Sunny,

Yes you are right Mike should have been included. This is the sample dataset that i prepared to get my actual dataset working - so i used the same expression on my real dataset - I only need to count the users email when there is an email associated with 'in cotract' and there is an another email associated with 'not in contract'

this expression is counting all when there is "in contract" or "not in contract" say for example for 11/13

11/13 ABC,X abc@yahoo.com In Contract

11/13 ABC,X abc@gmail.com Not in Contract

since this user has 2 emails - 1 associated with in contract and 1 associated with not in contract i count this as 2

11/13 XYX,Z xyx@yahoo.com In Contract

I dont count this user at all as his email is not associated with both "in contract" and "not in contract"

attached the dataset that i was using

again, thanks a lot for helping with this expression

Re: need help with the expression

RheaQv12 — Thu, 14 Nov 2019 01:55:03 GMT

11/13 YYY,Z yyy@yahoo.com In Contract

11/13 YYY,Z yyy@gmail.com In Contract

another example where the user has 2 diff emails but the scope is same - i dont need to count this record

i need to count only when the user has 2 diff emails with 2 diff scopes like below

11/13 David,David david@gmail.com In Contract

11/13 David,David david@yahoo.com Not in Contract

i count this user with 2 as no of emails

please let me know if this is not clear

Re: need help with the expression

sunny_talwar — Thu, 14 Nov 2019 12:08:10 GMT

What is the issue with the expression I gave? Is that not working? Can you share a scenario where it isn't doing what you need it to do?

Re: need help with the expression

RheaQv12 — Thu, 14 Nov 2019 14:45:16 GMT

Hey Sunny, thanks a lot

when we say scope={'In Contract','Not In Contract'} = This will take into users scope when it is "in contract" or "not in contract" - right? so it is calculating the distinct email ids when the scope is either one of this - right?

i only need to consider if the user has scope as "in contract' and there is an email id and also there is scope "not in contract" and there is also another email with it - in the below example the attestation in each row is "in contract" or (in scope ) i just used in contract for the sample data

my expression is - attached some sample data - again, really appreciate your time

=COUNT(Distinct {<FirstNameLastName={"=Count(Distinct {<Attestation={'In Scope','Not in Scope'}>} Email) >1" }>} Email)

Re: need help with the expression

RheaQv12 — Thu, 14 Nov 2019 18:02:29 GMT

Hey Sunny, did i confuse you?

Re: need help with the expression

sunny_talwar — Thu, 14 Nov 2019 20:34:31 GMT

Try this

=Count(DISTINCT {<FirstNameLastName = {"=Count(DISTINCT {<Attestation = {'Not in Scope'}>} Attestation) = 1 and Count(DISTINCT {<Attestation = {'In Scope'}>} Attestation) = 1 and Concat(DISTINCT {<Attestation = {'Not in Scope'}>} Attestation, ',') <> Concat(DISTINCT {<Attestation = {'In Scope'}>} Attestation, ',')"}>} Email)

Re: need help with the expression

RheaQv12 — Fri, 15 Nov 2019 17:10:56 GMT

Hey Sunny,

thanks a lot..you are truly genius!

Re: need help with the expression

RheaQv12 — Fri, 15 Nov 2019 17:17:45 GMT

Hey Sunny,

what it does is - count the persons emails only when there is at least 1 in scope attestation and 1 out of scope attestation and the attestation should not be same then only count the emails - right? thanks a lot

Re: need help with the expression

sunny_talwar — Fri, 15 Nov 2019 18:03:25 GMT

Mostly yes except the last part where it checks if for a attestation they have different emails

Emails for not in scope

Concat(DISTINCT {<Attestation = {'Not in Scope'}>} Attestation, ',')

Emails for in scope

Concat(DISTINCT {<Attestation = {'In Scope'}>} Attestation, ',')