topic Re: Aggregate over data in same table except self (Data load script, Qlik Sense) in Connectivity & Data Prep

Aggregate over data in same table except self (Data load script, Qlik Sense)

Thu, 04 Feb 2016 04:47:06 GMT

I have data that I want to also display data from the years previous, we are using Years/Weeks rather than Years/Months/Days so I'll use that in my example.

Currently I have a table similar to this;

Year	Week	A	B
2016	1	10	15
2016	2	5	10
2015	1	12	18
2015	2	6	18
2014	1	3	12

Now I want it to just be this year, but to add another field based on the previous years with the same week.
So for example;

C= B/A

Week 1, (18+12)/(3+12)=2

Week 2, 18/6=3

Year	Week	A	B	C
2016	1	10	15	2
2016	2	5	10	3

Obviously I need it for all 52 weeks of the year and there are also many more columns, but they should be irrelevant regarding this example.

I have thought to make for each 1 to 52 load and save it into 52 temporary qvds and join them all up.
If it was possible to nested load, so load with condition of the weeks being equal but year beeing less than, then aggregating that, it would work but it doesn't look like that is possible.

I could create another temp table with all 2016 years taken out, then aggregate based on week, then join back to the original table, this is the one I will probably work on until/if a easier response is stated. I'm sure it's possible in an easy way I just can't seem to see/find it.

Thanks for your time.

Ryan

Re: Aggregate over data in same table except self (Data load script, Qlik Sense)

paul_scotchford — Thu, 04 Feb 2016 07:32:47 GMT

Hey Ryan

Firstly, why aggregate in the loader,we try to avoid that unless a very real need to do it ?

Create a master calendar , one that has weeks for you and then in your app you will make calculation expressions to return values for Year/Week periods and so on.

Here is a link to start the ball rolling for you with Calendars ...

Master Calendar Data

Paul

Re: Aggregate over data in same table except self (Data load script, Qlik Sense)

Thu, 04 Feb 2016 08:30:26 GMT

This data set is over 30 million data rows, and many more columns, with multiple tables extended off.
Hence why I'm trying to do essentially as much processing as possible in the load.

I don't care about days at all, the original data is actually a calendar entry but I literally only need the Year and Week, it's easier to deal with just those two than having them in one, that is a very minor part of this and I don't think it really adds to the complexity of it anyway.

Even adding up the for 'this' single year, all the weeks, takes a couple of seconds to computer adding more just makes it slower and slower until it times out and won't display the data.

Re: Aggregate over data in same table except self (Data load script, Qlik Sense)

Thu, 11 Feb 2016 01:53:23 GMT

Hi Ryan,

The following script will add the data from the previous year.

1	Data:
2	Load * inline
3	[
4	Year,Week,A,B
5	2016, 1, 10, 15
6	2016, 2, 5, 10
7	2015, 1, 12, 18
8	2015, 2, 6, 18
9	2014, 1, 3, 12
10	];
11
12	Left Join (Data)
13	Load
14	Year + 1 as Year,
15	Week,
16	A as A_LY,
17	B as B_LY
18	Resident Data;
19
20	Left Join (Data)
21	Load
22	*,
23	(B+A) / (B_LY + A_LY) as C
24	Resident Data;

Re: Aggregate over data in same table except self (Data load script, Qlik Sense)

Thu, 11 Feb 2016 05:22:34 GMT

Incase anyone was wondering the best way I found to solve this is to filter into a another table without the current years, then we can just sum and divide over the fields as the year is irrelevant, then just attaching it to the newest year as necessary.

Looking to do the same with weeks now, most likely going to do the same but with a do for 1...52 instead.

Let me know if there is an easier way of doing this.