How do I Check Transaction amount against reference table

[#1]

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:

LoanType	State	TransDate	TransType	TransAmount
VA	FL	3/1/2012	AttorneyFee	500
FHA	FL	3/1/2012	AttorneyFee	700
VA	WI	3/1/2012	AttorneyFee	450
FHA	WI	3/1/2012	AttorneyFee	900
VA	FL	4/1/2012	AttorneyFee	650
VA	WI	4/1/2012	AttorneyFee	1200
FHA	FL	5/1/2012	PropertyFee	2000
FHA	WI	5/1/2012	AttorneyFee	800

The Reference Table looks like this:

State	VAAttorneyFeeLimit	FHAAttorneyFeeLimit	PropertyFee
FL	600	550	1000
WI	750	500	1000

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

LoanType	State	TransDate	TransType	TransAmount	OverLimit
VA	FL	3/1/2012	AttorneyFee	500	N
FHA	FL	3/1/2012	AttorneyFee	700	Y
VA	WI	3/1/2012	AttorneyFee	450	N
FHA	WI	3/1/2012	AttorneyFee	900	Y
VA	FL	4/1/2012	AttorneyFee	650	Y
VA	WI	4/1/2012	AttorneyFee	1200	Y
FHA	FL	5/1/2012	PropertyFee	800	N
FHA	WI	5/1/2012	AttorneyFee	800	Y

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!

[fosuzuki]

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

[#3]

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

[Anonymous]

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

[#5]

Could you expand on that a little?  If I'm comparing multiple values, can I nest my if statements?  Also, what is the syntax for the load?  I provided this as an example, but what I'm doing is actually a little more complicated.  If someone could provide an example script of how they could do this, then I'm hoping I can expand on that for my report.  Thanks!

[fosuzuki]

Hi afelston,

Check the attached qvw.

Hope this helps you

Regards

Fernando