9 Replies Latest reply: Dec 30, 2013 10:50 AM by glen sibley RSS

    Script/Expression Help Required

      Hi

       

      I am new to Qlikview and would appreciate some asistance on a problem I can't find a solution for.

       

      I have three tables:

       

      Locks:

      CONTRACT ACCOUNT

      LOCK TYPE

      FROM

      TO

       

      Workstream:

      CONTRACT ACCOUNT,

      CONTRACT NUMBER,

      DOCUMENT NUMBER,

       

      Balance:

      CONTRACT ACCOUNT,

      CONTRACT NUMBER,

      BALANCE,

       

      What I am trying to do is create a straight table that totals the balance of Contract Accounts that have a Lock Type of '1' that are not in the Workstream table.

       

      I'm using the following expression:

       

      sum

       

      ({<[LOCK TYPE = {'1'}>}if(isnull(CONTRACT NUMBER), BALANCE, 0)) 

       

      But because there are multiple contract accounts with 'u' type locks, it multiples the total balance by the number of times it features in the table.

       

      Any help would be much appreciated,


      Thanks,


      Glen