Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

need help with the expression

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

Capture.JPG

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

 

1 Solution

Accepted Solutions
Highlighted

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)

View solution in original post

14 Replies
Highlighted

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

Highlighted
Creator
Creator

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 -

 
 
Highlighted
Creator
Creator

Capture2.JPG

Sunny, thanks a lot

Highlighted
Creator
Creator

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"

 

Highlighted

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)
Highlighted
Creator
Creator

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

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

 

 

 

 

 

Highlighted
Creator
Creator

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

Highlighted

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?

Highlighted
Creator
Creator

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

Capture12.JPG

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)