topic Single and double intervalmatch mixed in QlikView

Single and double intervalmatch mixed

amien — Wed, 15 Jun 2011 21:05:51 GMT

I have let was this intervalmatch range (gl_account)

100 - 199

200 - 299

300 - 399

I need for the last category another intervallmatch, because these costs are placed

Under a certain cost place range.

Anothe issue is that some gl account are mixed. So are both in the 200-299 category and also gl account under the 300-399 (including the specific costplace)

How can this be fixed?

Single and double intervalmatch mixed

amien — Wed, 15 Jun 2011 21:08:00 GMT

Let was = lets say

Single and double intervalmatch mixed

prieper — Wed, 15 Jun 2011 21:13:59 GMT

Define a rule?

Peter

Single and double intervalmatch mixed

amien — Wed, 15 Jun 2011 21:16:24 GMT

What do you mean?

Single and double intervalmatch mixed

amien — Wed, 15 Jun 2011 21:22:30 GMT

Intervalmatch1 | intervalmatch2

------------------------------------

100...........199 |

200...........299 |

300...........399 | 9000....... 9050

300...........399 | 9060........9800

Interval1 = gl account

Intetval2. = costplace

Single and double intervalmatch mixed

prieper — Wed, 15 Jun 2011 21:29:43 GMT

How should this work without duplicating the data?

Peter

Single and double intervalmatch mixed

amien — Wed, 15 Jun 2011 21:34:22 GMT

Where do you see duplication? 300-399 has an extra intervalmatch

Single and double intervalmatch mixed

Wed, 15 Jun 2011 22:59:18 GMT

I don't see any IntervalMatch request in your post. I can see that you try to define accounts chart for your GL to describe your cost centers. Where and how are you going to use IntervalMatch functionality?

Re: Single and double intervalmatch mixed

amien — Thu, 16 Jun 2011 04:28:18 GMT

fact_table:

Key, GL_Account, CostPlace, Value, Brand, .....

intervalmatch1:

LOAD * INLINE [

from, to, text

100 - 199, inventory

200 - 299, unknown

300 - 399, indirect costs

100 - 399, subtotal I

]

intervalmatch2:

LOAD * INLINE [

from, to ,text

9000, 9050, indirect costs - costplace I

9060, 9800, indirect costs - costplace II

9000, 9050, subtotal I

9060, 9800, subtotal I

];

INTERVALMATCH (GL_Account)

LOAD

from,

RESIDENT intervalmatch1;

INTERVALMATCH (CostPlace)

LOAD

from,

RESIDENT intervalmatch2;

Re: Single and double intervalmatch mixed

amien — Thu, 16 Jun 2011 06:43:52 GMT

if could use alot of IF statements instead of intervalmatch . but than i have a problem with the subtotals as dimensions.

also tried to combine GL_account with costplace (1009000 - 1999050 or 9000100 - 905199) as a interval, but than i have an overlapping issue ofcourse.

Re: Single and double intervalmatch mixed

Thu, 16 Jun 2011 14:11:22 GMT

see attachment

Re: Single and double intervalmatch mixed

amien — Thu, 16 Jun 2011 15:36:01 GMT

Thanks .. i will look into it first thing tomorrow ..

whats the purpose of this?

Left Join (fact_table)

LOAD

RESIDENT intervalmatch1;

Re: Single and double intervalmatch mixed

Thu, 16 Jun 2011 15:43:57 GMT

Amien wrote:
Thanks .. i will look into it first thing tomorrow ..
whats the purpose of this?
Left Join (fact_table)
LOAD
*
RESIDENT intervalmatch1;

to deliver "text" fields to fact_table records.

Re: Single and double intervalmatch mixed

amien — Thu, 16 Jun 2011 20:08:41 GMT

Nick,

* interval2 needs to be based on both glaccount number and costplace.

* text1 and text2 should be 1 dimension, not 2

So, this row:

4, 301,9070, 3425, DDDD

is indirect costs because 301 falls into 300-399 PLUS the costplace falls into the 9060-9800 range.

1, 101,9040, 1000, AAA

does not fall into the indirects costs because 9040 falls into the 9000-9050 range, but GL_account is 101 and does not fall into the 300-399 range.

in the end i need 1 dimension in the chart (text1 for the GL accounts only category and text 2 for the costplaces). Mind that some categories are a mix from both GL accounts only values and GL accounts and costplaces values. Plus there are subtotals.

shoot when you need more information

Re: Single and double intervalmatch mixed

amien — Fri, 17 Jun 2011 06:26:12 GMT

this fixed my issue

IF(GLAccount > 300000 and < 399999,Costplace,GLAccount) AS GL_Account2;

now i only need 1 intervalmatch, plus i can create subtotals in the intervalmatch, without creating too much lines

Re: Single and double intervalmatch mixed

Fri, 17 Jun 2011 13:05:05 GMT

Amien wrote:
this fixed my issue
IF(GLAccount > 300000 and < 399999,Costplace,GLAccount) AS GL_Account2;
now i only need 1 intervalmatch, plus i can create subtotals in the intervalmatch, without creating too much lines

We are not telepathists to read your mind for application logic. It's up to you. But this is definitely not the answer for the topic of the post.