Using Intervalmatch on two dimension

[#1]

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 🙂

[rwunderlich]

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

-Rob

[#3]

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

[rwunderlich]

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

[#5]

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