topic Re: RecNo aggregated by some fields in QlikView

RecNo aggregated by some fields

Anonymous — Wed, 15 Feb 2017 09:07:26 GMT

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

Re: RecNo aggregated by some fields

rahulpawarb — Wed, 15 Feb 2017 09:40:53 GMT

Hello Андрей,

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

Regards!

Rahul

Re: RecNo aggregated by some fields

Anonymous — Wed, 15 Feb 2017 10:07:57 GMT

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

Re: RecNo aggregated by some fields

swuehl — Wed, 15 Feb 2017 10:11:57 GMT

LOAD Agent,

Day,

Point,

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

FROM

RecNo.xlsx

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

Re: RecNo aggregated by some fields

dpduran — Wed, 15 Feb 2017 10:11:58 GMT

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

Re: RecNo aggregated by some fields

rahulpawarb — Wed, 15 Feb 2017 10:14:25 GMT

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

Re: RecNo aggregated by some fields

Anonymous — Wed, 15 Feb 2017 11:56:56 GMT

Thanks!

Re: RecNo aggregated by some fields

sunny_talwar — Wed, 15 Feb 2017 12:11:51 GMT

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;

Re: RecNo aggregated by some fields

Anonymous — Wed, 15 Feb 2017 12:45:33 GMT

Thanks, Sunny!

Re: RecNo aggregated by some fields

rahulpawarb — Wed, 15 Feb 2017 13:27:29 GMT

Super