topic Re: Creating 0 value for each hour in QlikView

Creating 0 value for each hour

amien — Wed, 25 Nov 2020 16:16:04 GMT

I have 1 table with three colums:

%Id, Zone,Hour

0, A, 10

1, A, 11

2, A, 12

3, B, 1

3, B, 2

etc

In my bar chart i have:

dim: Hour

exp : Count(%Id)

Now i want a 0 for each hour

So for Zone A, Where Hour = 10,11,12 there is a 1, the rest of the hours (all 24) have a zero.

I know you need to add a zero for each dim.

For i would do something like this:

t2:

LOAD Distinct

Zone

Resident t1

Outer Join

LOAD Distinct

Hour

Resident t1;

And then concatenate t2 to t1.

Note: Hour has every needed value (0..23)

This works..

But the thing is that my source is a huge table with alot of selectable dims. Isn't there a more easy way to do this?

Re: Creating 0 value for each hour

sunny_talwar — Thu, 26 Oct 2017 14:57:53 GMT

I don't think there is an easy way out here:

Generating Missing Data In QlikView

Re: Creating 0 value for each hour

amien — Mon, 30 Oct 2017 15:30:13 GMT

Thanks, but i don't really see how this helps? i could 0 values for each hour, but the problem is that i can't concatenate my fact table. Is it possible to fix this by adding a separate fact table?

Re: Creating 0 value for each hour

Sergey_Shuklin — Mon, 30 Oct 2017 17:53:49 GMT

Hello!

Not sure if it will be suitable for your task, but you can create a sample table which will contain all your needed distinct values like:

A,0

A,1

A,2

...

A,23

B,0

B,1

B,2

...

Z,23

Then you need to create an unique key (connection) with your main huge data table. This is actualy a common way to implement such tasks. Hope it will help!