topic hey in QlikView

hey

Mon, 26 Jan 2026 18:19:17 GMT

i have a field name Region which has north,south,east,west...my customer is asking that i should give him a functionality of north and South,East and West.......so that the region has 6 fields i.e, north,south,east,west,North and South,East and West

Re: hey

Tue, 15 Apr 2014 06:54:48 GMT

Hi Sanchit,

Can you please give a bit more details as I am unclear what you are trying to achieve.

Thanks,
Marius

Re: hey

rajat2392 — Tue, 15 Apr 2014 07:06:22 GMT

please provide a sample data so that I can create a solution and send back to you..

Re: hey

Tue, 15 Apr 2014 07:46:58 GMT

Hi Sanchit,

How do you define the new "North and South" region? Is that the sum of the north and south data?

If yes, that you can't add the new region to the same dimension because it is an aggregation.

I think that what you trying to achieve is to transform this:

into this:

That way you can display the data by the "top" region and than drill down to "sub" regions.

In your script you could use something like this:

pick(wildmatch(Region, 'North', 'South', 'East', 'West'),'North and South', 'North and South', 'East and West', 'East and West') as [TopRegion]

Cheers,

Paul

Re: hey

ngulliver — Tue, 15 Apr 2014 07:58:00 GMT

Hi,

You will need clarification from the customer exactly what he means by North East etc. Are they asking for it to be a combination of North and East values or something more individual. Or is the input data going to change ?

Re: hey

rajat2392 — Tue, 15 Apr 2014 12:28:04 GMT

I think you require this, check the application attached if it helps...

Re: Re: hey

rajat2392 — Tue, 15 Apr 2014 13:20:19 GMT

or you can simply do this...

Re: hey

Wed, 16 Apr 2014 01:02:25 GMT

Hi Rajat,

But if the N-S and E-W are the sums of their respective regions than you are mixing different levels of granularity in the same dimension. This will lead to incorrect calculations (unless you use set analysis).

Cheers,

Paul

Re: hey

rajat2392 — Wed, 16 Apr 2014 04:05:53 GMT

what is the requirement exactly then?

your customer is asking for two more values N-S and E-W in the dimension Region..what would be their values then..their sum up only or something else?

Re: hey

Wed, 16 Apr 2014 07:16:19 GMT

what you showed is exactly what i want but i cant use if statement as my data set is large

Re: hey

rajat2392 — Wed, 16 Apr 2014 08:45:25 GMT

what you can do then is extract out one more table out of the main table having distinct region and the sum of their values, then the new table will have only 4 regions and their values as in this example. Then you can apply the same thing on that new table.

Re: hey

rajat2392 — Wed, 16 Apr 2014 09:09:22 GMT

Hi Paul,

the level of granularity is decided by you only. How you want to see and represent, you create a model for that purpose only. If the client wants to see the North-South summed up data, then you have to create like this one.

Re: hey

Wed, 16 Apr 2014 16:44:48 GMT

Hi Rajat,

I must respectfully disagree with you. We are not talking here about deciding the level of granularity but about mixing the levels of granularity. Mixing granularity is always a bad idea. People inevitably sum the higher grain measures producing incorrect reporting. If a client ask a simple question - what is the total sum of sales for all regions? You can't simply do a sum here without excluding the aggregated regions first. The developer is just making life really hard for himself or herself in my opinion.

If this is what clients wants and the consultant can't see anything wrong with this request than I would be worried.

Keep to the Grain in Dimensional Modeling - Kimball Group

Fistful of Flaws - Kimball Group

When developing fact tables, aggregated data is NOT the place to start. To avoid “mixed granularity” woes including bad and overlapping data, stick to rich, expressive, atomic-level data that’s closely connected to the original source and collection process." - Ralph Kimball

Cheers,

Paul

Re: hey

rajat2392 — Thu, 17 Apr 2014 04:33:57 GMT

Hi Paul,

I am not saying that I disagree with you. I totally agree with you, mixing up is never a good idea. But here I am not dealing with Sanchit's client, else I would have suggested them something else that what could be a better option for them to analyze. But here what I am doing is just helping him with the script, just to get the desired result. I think you can understand this now.

Thanks

Rajat