Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

RecNo aggregated by some fields

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

AgentDayPointPosition
1MoA1
1MoB2
1MoC3
1MoD4
1TuA1
1TuB2
1TuC3
1TuD4
1WeA1
1WeB2
2MoA1
2MoB2
2MoC3
2MoD4
2TuA1
2TuB2
2TuC3
2TuD4
2WeA1
2WeB2
9 Replies
rahulpawarb
Specialist III
Specialist III

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
Not applicable
Author

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

swuehl
MVP
MVP

LOAD Agent,

     Day,

     Point,

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

FROM

RecNo.xlsx

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

dpduran
Contributor III
Contributor III

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
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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;

Capture.PNG

Anonymous
Not applicable
Author

Thanks, Sunny!

rahulpawarb
Specialist III
Specialist III

Super