topic Re: Connect data island via expression in QlikView

Connect data island via expression

Tue, 04 Oct 2011 14:36:52 GMT

I have a complete data model with fact and dimensions. This model uses section access to reduce the data. I have an aggregated table that is completely unconnected to my model. This is used to prevent the data reduction to take place on that table. I am not succeeding in connecting the table via an expression.

Below you will find a simplified example of my problem.

Revenue:
LOAD * INLINE [
    Week number, Store, Department, Revenue
    1, Adam, Pants, 25
    1, Eve, Skirts, 15
    1, Eve, Hats, 20
    2, Adam, Sweater, 55
    2, Adam, Pants, 50
    2, Eve, Skirts, 35
    3, Adam, Shoes, 25
    3, Eve, Hats, 40
];

Goal:
LOAD * INLINE [
    _Week number, _Store, Goal
    1, Adam, 30
    1, Eve, 20
    2, Adam, 120
    2, Eve, 30
    3, Adam, 25
    3, Eve, 35
];

I want to create the following table with selection options:

The goal should give the goal for each week and each particular store. It should also respond to the selections I make in the listboxes.

I have attached the qvw as an example.

What is the best way to handle this?

Re: Connect data island via expression

lironbaram — Tue, 04 Oct 2011 14:47:41 GMT

hei

attach is an example

hope it helps you

Re: Connect data island via expression

Wed, 05 Oct 2011 07:57:56 GMT

That works Liron, thank you. But I actually have a model that looks something like this:

Does this mean that I have to extend the Goal table with the Week number and Store fields in order to make it work? Or are there other ways to do it? I would like to keep my model as clean as possible.

(See attached qvw)

Re: Connect data island via expression

Fri, 14 Oct 2011 07:50:20 GMT

Is there anybody that can tell me what the best way to tackle the described problem is? I had oversimplified my example a bit. So the second one matches my actual situation a lot better.

Thanks

Re: Connect data island via expression

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 08:29:56 GMT

Hi,

It seems that your tables where not properly populated. The If() solution works, check the attached application.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Connect data island via expression

Fri, 14 Oct 2011 09:39:22 GMT

Hhhhm that's weird.

I have added a distinct to the IF to get the results right:

SUM(DISTINCT IF(%Store_key = _%Store_key AND %Week_key = _%Week_key, Goal))

But you are right, it does work with the if construction.

Thanks