topic Re: Aggregation where date is missing in QlikView

Aggregation where date is missing

Wed, 07 Jun 2017 15:24:30 GMT

Hello,

I’m trying to find out if there’s a more optimised way of achieving the following.

Example data set:

ID	Date	Created	Aggregated
A	01-May	66	66
A	02-May	71	137
A	03-May	24	161
A	04-May	76	237
A	05-May	5	242
B	01-May	82	82
B	02-May	77	159
B	04-May	59	218
B	05-May	91	309

* Note for ‘B’ – 03 May is missing.

So, if I create a summary by ID, the following results:

	A	B
02-May	137	159
03-May	161	0
04-May	237	218

However, I expect/want:

	A	B
02-May	137	159
03-May	161	159
04-May	237	218

i.e. where null, show the previous not-null value.

Alternatively; If I only looked at 03-May in isolation - filtering to this date only, I would see 0 for ‘B’ – when looking at something like ‘Open’ cases, this implies none are open, however 159 are still open from the previous day.

Currently, in the LOAD script, I use a list of all dates and LEFT JOIN on the data to fill the gaps. Then the following;

If(AutoNumberHash128(ID)=AutoNumberHash128(Peek('ID')), Peek(Aggregated)+(IF(LEN([Aggregated]),[Aggregated],0)), (IF(LEN([Aggregated]),[Aggregated],0))) AS [Aggregated]

Essentially; IF the ID matches the Previous ID THEN Previous Aggregated + Additional Aggregated = New Aggregated

But for this I have to add dates to the data set where there is no value, just to fill the aggregation.

To achieve the above I need to artificially create nearly 5 times as many rows as have actual data.

Is this the best way to resolve this issue, or is there a smarter way with an Expression?

Maybe loading all that data isn’t an issue, and having an Expression to achieve the above would impact performance – I just want to be sure I’m applying the best method.

Thanks in advance.

Re: Aggregation where date is missing

sunny_talwar — Wed, 07 Jun 2017 15:35:29 GMT

This link might be a useful place to begin

Generating Missing Data In QlikView

Re: Aggregation where date is missing

Sergey_Shuklin — Wed, 07 Jun 2017 17:03:15 GMT

Hello, Noalen!

Not long ago the same problem was solved:

Previous Value

And, please, check the Sunny's link - there are a lot of usefull and clearly described methods of fullfillin a missing data.

Re: Aggregation where date is missing

Thu, 08 Jun 2017 08:25:43 GMT

Thanks Sunny. That seems to be what I needed. I think basically 'yes' I need the artificial rows. But this does give me some idea about tweaking the logic.

Re: Aggregation where date is missing

Thu, 08 Jun 2017 08:26:17 GMT

Thanks for replying Sergey. Sunny's link was more to what I needed, but I appreciate you replying.

Re: Aggregation where date is missing

sunny_talwar — Thu, 08 Jun 2017 15:32:02 GMT

That's awesome