Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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;

YearWeekAB
201611015
20162510
201511218
20152618
20141312

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

YearWeekABC
2016110152
201625103

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

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

4 Replies
paul_scotchford
Specialist
Specialist

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

Not applicable
Author

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.

Not applicable
Author

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;

View solution in original post

Not applicable
Author

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.