Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Adding calculated fields referencing interval match result to your load script

I'm generating a qvw to show financial transactions across all Companies (primarily country based) within our Group. So far I've extracted my transactional data (this includes transaction date, Company code and credit/debit values in domestic currency, e.g. GBP for our UK companies, US$ for our US company, DKK for our Danish company etc) into qvd files (one per financial year - plan is to show data from the current and last two financial years) and additionally I'm extracting GBP exchange rates (including Company code, valid from and valid to dates) direct from my primary datasource. I've then used INTERVALMATCH to associate the relevant GBP exchange rate per Company according to the transaction date and how it relates to the valid from/to dates for our GBP exhchange rates.

This is working great, but I'd like to know if it's possible to pre-calculate my GBP values (i.e. domestic credit/debit values per transaction multiplied by the associated GBP exchange rate) within my script rather than typing out a formula (e.g. DEBIT_AMOUNT * GBP_RATE) every time I need to display a GBP value.

I seem to tripping over syntax right now so would appreciate confirmation if this can be done and any tips on join syntax and placement with my script (e.g. I assume this would need to come after the transactional qvd load, the exchange rates datasource load and the INTERVALMATCH element). Thanks in advance to anyone who can help!

2 Replies
Not applicable

Adding calculated fields referencing interval match result to your load script

Best way is to JOIN the tables containing those fields in your formula. If that is possible of course.

Then you can add 'DEBIT_AMOUNT * GBP_RATE AS [GBP value]' to the table.

Not applicable

Adding calculated fields referencing interval match result to your load script

Thanks for responding Mark. Issue I have is that (due to volume of transactional data), joining the tables to create a "GBP_VALUE" field results impacts too heavily upon reload time (time taken more than triples). IntervalMatch function has worked really well in terms of load performance (we only have approximately 3,500 possible match combinations for GBP exchange rate), but I'm now looking to improve application performance through scripting "GBP_VALUE" (in relation to the output from the IntervalMatch result). Will also reduce workload when wanting to write object expressions including this value.

Any ideas on correct syntax for joining the IntervalMatch result to a preceding load table?

Community Browser