Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

Average sum group by months in load script

Good day!

In load script i load table from excel:

Main:

LOAD

  Id,

  Year(Date)&num(Month(Date),'00') as YearMonth,

  avg(Sum)

  FROM

$(DataPath)Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

in result i get table:

  

IdDateSum
101.01.20154
102.01.20156
103.01.20154
104.01.20156
105.01.201537
106.01.20154
107.01.20152
108.01.20157
109.01.20154
101.02.20154
102.02.20156
103.02.20153
104.02.20156
105.02.20153
106.02.20151
107.02.20153
108.02.20155
109.02.201576

How i load script calculate average sum group by YearMonth?

My necessary result:

Id     YearMonth     Sum

1     201501     8.2

1     201502    11.8

Thanks.

1 Solution

Accepted Solutions
giakoum
Honored Contributor II

Re: Average sum groub by months in load script

Main:

LOAD

  Avg(Sum) as AvgSum,

  YearMonth

Group by

  YearMonth;

LOAD

  Id,

  Year(Date)&num(Month(Date),'00') as YearMonth,

  Sum

  FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

11 Replies
giakoum
Honored Contributor II

Re: Average sum groub by months in load script

Main:

LOAD

  Avg(Sum) as AvgSum,

  YearMonth

Group by

  YearMonth;

LOAD

  Id,

  Year(Date)&num(Month(Date),'00') as YearMonth,

  Sum

  FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

Re: Average sum groub by months in load script

Data:

Id,

  Year(Date)&num(Month(Date),'00') as YearMonth,

  Sum

  FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Group:

noconcatenate

load Id,

YearMonth,

avg(Sum) as AvgSum

Resident Data

group by Id,YearMonth;


drop table Data;

andreyfcdk91
New Contributor III

Re: Average sum group by months in load script

Thanks!!

How i can add filed id Id to result table?

awhitfield
Esteemed Contributor

Re: Average sum groub by months in load script

Hi,

Just set the Dimension to YearMonth and the expression to Avg(Sum) and format the number as Integer 1dp

And you'll get:

Just add ID as another dim if required.

HTH Andy

andreyfcdk91
New Contributor III

Re: Average sum group by months in load script

Thanks for advice.

But i need to do it in script level.

giakoum
Honored Contributor II

Re: Average sum group by months in load script

depends

if you have multiple ids per year-month (and I guess you do) then you can't

How do you want it to look?

andreyfcdk91
New Contributor III

Re: Average sum group by months in load script

Id     YearMonth     Sum

1     201501     8.2

1     201502    11.8

giakoum
Honored Contributor II

Re: Average sum group by months in  load script

is id always 1?

if yes, just add min(id) as id in the first load

giakoum
Honored Contributor II

Re: Average sum group by months in load script

Main:

LOAD

  min(Id) as Id,

  Avg(Sum) as AvgSum,

  YearMonth

Group by

  YearMonth;

LOAD

  Id,

  Year(Date)&num(Month(Date),'00') as YearMonth,

  Sum

  FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

Community Browser