RecNo aggregated by some fields

[Anonymous]

Good day!

Colleagues, what function in load script can calculate number of row, aggregated by some list of field?

For example, i have table and in load script i need ti calculate file Position, aggregated by Agent and Day - All values of Points must have values from 1 to Quantity of Points in Agent-Day Range

Agent	Day	Point	Position
1	Mo	A	1
1	Mo	B	2
1	Mo	C	3
1	Mo	D	4
1	Tu	A	1
1	Tu	B	2
1	Tu	C	3
1	Tu	D	4
1	We	A	1
1	We	B	2
2	Mo	A	1
2	Mo	B	2
2	Mo	C	3
2	Mo	D	4
2	Tu	A	1
2	Tu	B	2
2	Tu	C	3
2	Tu	D	4
2	We	A	1
2	We	B	2

[rahulpawarb]

Hello Андрей,

Create a straight table with Agent, Day & Point as dimension and add RowNo() as expression. This will give you expected results.

Regards!

Rahul

[Anonymous]

Thanks. But all pre-calculations i must implement in load script, because final result must be saved into qvd file also in LS.

[swuehl]

LOAD Agent,

     Day,

     Point,

     AutoNumber(Recno(), Agent&Day) as Position

FROM

RecNo.xlsx

(ooxml, embedded labels, table is Лист1);

[dpduran]

if you just want to add the row number you can add the function in the script:

LOAD Agent,

     Day,

     Point,

    RowNo()

FROM

RecNo.xlsx

(ooxml, embedded labels, table is Лист1);

Please note the difference between using RecNo() and RowNo() which won't give same results --> rowno() and recno()

[rahulpawarb]

I 100% agree with Stefan! You can implement the approach suggested by him.

[Anonymous]

Thanks!

[sunny_talwar]

Another method using Previous function

Table:

LOAD Agent,

    Day,

    Point

FROM

RecNo.xlsx

(ooxml, embedded labels, table is Лист1);

FinalTable:

LOAD *,

  If(Agent = Previous(Agent) and Day = Previous(Day), RangeSum(Peek('Position'), 1), 1) as Position

Resident Table

Order By Agent, Day;

DROP Table Table;

[Anonymous]

Thanks, Sunny!

[rahulpawarb]

Super