topic Re: How to get total in data loader in QlikView

How to get total in data loader

Anonymous — Thu, 14 Dec 2017 10:28:34 GMT

Hello!

I have

year	month	value
2015	1	100
2015	2	600
2015	3	50
2016	2	300
2016	4	500
2016	8	800
2017	5	20
2017	7	40
2017	10	170
2017	12	600

And I want to calculate the share of each month in relation to the year.

In chart I can do it like this:

sum(total<month> value)/sum(total<year> value)

How can I do it in data loader?

Thanks.

Re: How to get total in data loader

sunny_talwar — Thu, 14 Dec 2017 16:36:55 GMT

Try this

Table:

LOAD * INLINE [

year, month, value

2015, 1, 100

2015, 2, 600

2015, 3, 50

2016, 2, 300

2016, 4, 500

2016, 8, 800

2017, 5, 20

2017, 7, 40

2017, 10, 170

2017, 12, 600

];

Left Join (Table)

LOAD year,

Sum(value) as YearlySum

Resident Table

Group By year;

Left Join (Table)

LOAD month,

Sum(value) as MonthlySum

Resident Table

Group By month;

Re: How to get total in data loader

Anonymous — Wed, 03 Jan 2018 10:01:18 GMT

Thank you very much!

It works with small amounts of data. But the script is not executed if there are many entries in the tables.

Re: How to get total in data loader

sunny_talwar — Wed, 03 Jan 2018 13:08:34 GMT

Try this

Table:

LOAD * INLINE [

year, month, value

2015, 1, 100

2015, 2, 600

2015, 3, 50

2016, 2, 300

2016, 4, 500

2016, 8, 800

2017, 5, 20

2017, 7, 40

2017, 10, 170

2017, 12, 600

];

Temp:

NoConcatenate

LOAD *

Resident Table

Order By year;

Left Join (Table)

LOAD year,

Sum(value) as YearlySum

Resident Temp

Group By year;

DROP Table Temp;

Temp:

NoConcatenate

LOAD *

Resident Table

Order By month;

Left Join (Table)

LOAD month,

Sum(value) as MonthlySum

Resident Temp

Group By month;

DROP Table Temp;

See if the above help you with the reload time....

Optimize Group By Performance