0 Replies Latest reply: Nov 22, 2011 2:42 AM by Gerhard Laubscher RSS

    Count or Load distinct

    Gerhard Laubscher

      Hi,

       

      I am running a Credit Limit Increase Campaign for my company and need to track the results (I SMS certain customers, they apply for a limit increase, our system does some checks, sends detail to the credit bureau and approves or declines them).

       

      Currently our system generates a daily approve/decline report, which I import into QlikView.

       

      Certain people try their luck twice or more - they will be approved, then try again - then the system will decline them because they recently had an increase.

      Other people will be declined for affordability, then try again via USSD and change their gross income and then they might be approved.

       

      People who apply more than once mess up the figures - I want to only count them once, BUT, if they are approved I want to count the approval, and not the decline - whether it happened first or last. If they are declined more than once I only want to count the first occurence.

       

      So I have two questions:

       

      1. If I use Count (DISTINCT [ID No]) - will it count the first or the last occurence loaded? Can I add set analyses to specify that it should use the occurence with the minimum or maximum date?

       

      2. Where a person was approved once and declined once or more - what should my expression be if I want to count that person only once - and only the approval? Let's say I want to count my number of declines. Currently my expression is: Count ({<Decision={'Declined'}>}[ID No])

      I only want to count the declines where the same ID number has no approval decisions.

       

      I've attached an image of the sheet where this is applicable - it's very basic calculations, but I'm not very good with QV. The "Reason for Declines" table total should be equal to the number of declines in the summary table, and should obviously also match back to the approval rate - so these duplicates affect a lot of my expressions.

       

      An even better solution for ALL of this would be if I could add this to my script so I only LOAD the duplicates once - i.e. if the ID number occurs more than once time, only load where the decision is = approved, and if there is no approval, only load the occurance with the min/max date.

       

       

       

      Please assist.

       

      Thanks,

       

      Gerhard