5 Replies Latest reply: Jun 16, 2012 4:54 PM by Aron Elston RSS

    How do I Check Transaction amount against reference table

      I'm pretty new to Qlikview but have been using Spotfire for a while and am trying to get used to the differences in the tool.  I've come across an issue that I can't quite wrap my brain around and I'm hoping someone can get me pointed in the right direction.

       

      Basically I have Two Tables.  One table has transactional data and the other is a reference table that has attributes I need to reference from the transactional table to determine if transactions have exceeded a specific amount depending on the State in which the transaction happened:

       

      Transactional Table looks like this:

       

      LoanTypeState
      TransDate
      TransTypeTransAmount
      VAFL3/1/2012AttorneyFee500
      FHAFL3/1/2012AttorneyFee700
      VAWI3/1/2012AttorneyFee450
      FHAWI3/1/2012AttorneyFee900
      VAFL4/1/2012AttorneyFee650
      VAWI4/1/2012AttorneyFee1200
      FHAFL5/1/2012PropertyFee2000
      FHAWI5/1/2012AttorneyFee800

       

      The Reference Table looks like this:

       

      State
      VAAttorneyFeeLimit
      FHAAttorneyFeeLimit
      PropertyFee
      FL6005501000
      WI7505001000

       

       

      So, given the values in this table, I'd like to have the following result:

       

       

      LoanTypeState
      TransDate
      TransTypeTransAmount
      OverLimit
      VAFL3/1/2012AttorneyFee500N
      FHAFL3/1/2012AttorneyFee700Y
      VAWI3/1/2012AttorneyFee450N
      FHAWI3/1/2012AttorneyFee900Y
      VAFL4/1/2012AttorneyFee650Y
      VAWI4/1/2012AttorneyFee1200Y
      FHAFL5/1/2012PropertyFee800N
      FHAWI5/1/2012AttorneyFee800Y

       

      For Attorney Fee limits it should check the loan type, state and Transaction Type, however for Property Fee it wouldn't matter if it is VA or FHA, it would just check State and TransType.   I think that I would do this in the edit script, but I'm open to pretty much any suggestions anyone would have to get me working in the right direction.  Thanks!

        • Re: How do I Check Transaction amount against reference table
          Fernando Suzuki

          Without putting too much effort on code-optimization, maybe you could try something like this:

           

          Transactions:

          LOAD ID, LoanType, State, TransDate, TransType, TransAmount

          FROM TransactionsTable;

           

          LEFT JOIN

          LOAD State, VAAttorneyFeeLimit, FHAAttorneyFeeLimit, PropertyFee

          FROM ReferenceTable;

           

          LEFT JOIN

          LOAD ID,

                    if(TransType = 'PropertyFee',

                         if(TransAmount > PropertyFee, 'Y', 'N'),

                         if(LoanType = 'VA',

                              if(TransAmount > VAAttorneyFeeLimit, 'Y', 'N'),

                              if(TransAmount > FHAAttorneyFeeLimit, 'Y', 'N')

                         )

                    ) AS FlagOverLimit

          Resident Transactions;

           

          This isn't too efficient due to the nested if-statements. And if you real case has more TransTypes and LoanTypes, the amount of ifs needed will increase.

           

          I've thought of a more optimized and flexible (in case of more TransTypes and LoanTypes) solution, but it would need a more complex coding... If you need it, let me know.

           

          Regards,

          Fernando

          • Re: How do I Check Transaction amount against reference table

            Hi Afelston

             

            The way that Qlikview works is pretty much dynamically through  "outer join" connections between tables. Tables are connected very simply through field names that are exactly the same.

             

            Literally all you need to do is script the two tables using those exact field names - they only have the state in common - and Qlikview will do the rest. You don't have to do anything to reference the different tables in the objects in the resulting dashboard. just create an object with the Loan type, state, transdate etc and it will do the join automatically.

             

            Is this what you wanted?

             

            Let me know

             

            Regards,

             

            Erica

            • Re: How do I Check Transaction amount against reference table
              Johannes Sunden

              Hi,

               

              One way off the top of my head would be to load the State and Ref Limit as a table and join with the transaction table. Then you can create an easy comparison in the load statement along the lines of:

              if(TransAmount>VAAttorneyFeeLimit, 'Y', 'N') as OverLimit