Pick match- A challange for someone who fancies it!

adamdavi3s — Mon, 21 Oct 2013 17:48:11 GMT

I'm baffled by this.

For reasons I won't go into, We have an island dimension, which has the same values as some column names in the fact table.

The simple reason being the dataset is 65,000,000 rows and having this dimension as an additional column would potentially increase this by 20 times!!

Therefore under the advice of qliktech we created the island dimension, and renamed the amount columns with the same values.

Consider this as a basic example:

Island Table:

cost_type
BLOOD
DRUGS
WARDS AND NURSING
IMPLANTS

Fact Table:

Header 1	Header 2	Header 3	Header 4	Header 5
HRG	BLOOD	DRUGS	WARDS AND NURSING	IMPLANTS
a	100	20	40	23
b	200	10	50	456

We did have this set up with a huge IF statement but the performance was appalling so on some very good advice here we used a pick match formula.

This works brilliantly... except when we built in a new variable divisor into the table when it all went a bit wrong.

The only way I've seen to calculate the right value is to use an aggr formula... this works fine for the first line within the pick / match but then fails??

The attached probably makes more sense.

Re: Pick match- A challange for someone who fancies it!

swuehl — Mon, 21 Oct 2013 18:02:39 GMT

Maybe like this?

Re: Pick match- A challange for someone who fancies it!

adamdavi3s — Mon, 21 Oct 2013 18:11:09 GMT

GAH!

So simple, I am such a pleb at times!!

topic Re: Pick match- A challange for someone who fancies it! in QlikView

Pick match- A challange for someone who fancies it!

Re: Pick match- A challange for someone who fancies it!

Re: Pick match- A challange for someone who fancies it!