Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
MVP

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)

Sum(value) as YearlySum

Resident Table

Group By year;

Left Join (Table)

Sum(value) as MonthlySum

Resident Table

Group By month;

3 Replies
MVP

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)

Sum(value) as YearlySum

Resident Table

Group By year;

Left Join (Table)

Sum(value) as MonthlySum

Resident Table

Group By month;

Anonymous
Not applicable
Author

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.

MVP

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

Resident Table

Order By year;

Left Join (Table)

Sum(value) as YearlySum

Resident Temp

Group By year;

DROP Table Temp;

Temp:

NoConcatenate

Resident Table

Order By month;

Left Join (Table)

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

Community Browser