Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

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
Valued Contributor II

Re: RecNo aggregated by some fields

Hello Андрей,

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

Regards!

Rahul

andreyfcdk91
New Contributor III

Re: RecNo aggregated by some fields

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

MVP
MVP

Re: RecNo aggregated by some fields

LOAD Agent,

     Day,

     Point,

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

FROM

RecNo.xlsx

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

dpduran
New Contributor III

Re: RecNo aggregated by some fields

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()

Highlighted
rahulpawarb
Valued Contributor II

Re: RecNo aggregated by some fields

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

andreyfcdk91
New Contributor III

Re: RecNo aggregated by some fields

Thanks!

Re: RecNo aggregated by some fields

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

andreyfcdk91
New Contributor III

Re: RecNo aggregated by some fields

Thanks, Sunny!

rahulpawarb
Valued Contributor II

Re: RecNo aggregated by some fields

Super

Community Browser