topic Using Intervalmatch on two dimension in QlikView

Using Intervalmatch on two dimension

Thu, 12 May 2011 12:37:19 GMT

Dear Sirs,

I am trying to create a P/L statement using Qlikview.

Normally i would have som postings on account numbers and then use intervalmatch to map the accounts on P/L positions, but my problem is that I have an extra dimension in my data set that i also need to use in the mapping sequense. The extra dimension is 'Division' which i have on every transaction:

Mapping:

Accounts Division Amount Needs to be posted in P/L statement

100-199 Hotel -200 Sales, Hotel

100-149 F&B -150 Sales, F&B

150-199 F&B -40 Sales, Other

200-210 Hotel 30 Payroll cost, Hotel

211-220 Overhead 50 Overhead cost

Is there a clever way to combine the intervalmatch function with the look-up in the Division-field?

Or do i need to have a 20-double if-statement?

Thx in advance 🙂

Using Intervalmatch on two dimension

rwunderlich — Thu, 12 May 2011 14:10:06 GMT

Look at "Extended Interval Match" in the Ref Guide.

-Rob

Using Intervalmatch on two dimension

Thu, 26 May 2011 13:24:02 GMT

Thanks, Rob. Unfortunately my data-set is not 100% consistent with the Division-dimension so i cant use the function anyway. But your answer is much appreciated.

Jesper

Using Intervalmatch on two dimension

rwunderlich — Thu, 26 May 2011 14:51:32 GMT

Take a look at the QV Cookbook example "Mapping numeric ranges.". You can add the Division field as part of mapping input table and then concatenate it to the generated map keys. Then use Division in the applyMap() as well. For example:

ApplyMap('acctmap', Account & Division)

-Rob

http://robwunderlich.com

Using Intervalmatch on two dimension

Sun, 06 May 2012 16:17:02 GMT

Hello Sir,

I've a table with Project Nos., StartdateProject,EnddateProject & Revenue

I need to a create a Calender so that on selecting a particular Year , all the projects starting or ending in this Year are displayed.

Moreover, on selecting the Quarters (Q1 or Q2 or Q3 or Q4) the Revenue should be shown only for that Quarter.

For eg. if a project has Yearly Revenue of 1000 & the project runs from 1 Jan, 2015 to 31 Dec, 2015

then on Selecting Q1, Revenue displayed should be 1000/4(=250).

Please help

Regards