Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregation where date is missing

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.

1 Solution

Accepted Solutions
sunny_talwar

This link might be a useful place to begin

Generating Missing Data In QlikView

View solution in original post

5 Replies
sunny_talwar

This link might be a useful place to begin

Generating Missing Data In QlikView

Sergey_Shuklin
Specialist
Specialist

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.

Not applicable
Author

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.

Not applicable
Author

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

sunny_talwar

That's awesome