Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
arman_muratov
New Contributor II

How to get total in data loader

Hello!

I have

yearmonthvalue
20151100
20152600
2015350
20162300
20164500
20168800
2017520
2017740
201710170
201712600

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.


Tags (1)
1 Solution

Accepted Solutions

Re: How to get total in data loader

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;

3 Replies

Re: How to get total in data loader

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;

arman_muratov
New Contributor II

Re: How to get total in data loader

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

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