Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hello Андрей,
Create a straight table with Agent, Day & Point as dimension and add RowNo() as expression. This will give you expected results.
Regards!
Rahul
Thanks. But all pre-calculations i must implement in load script, because final result must be saved into qvd file also in LS.
LOAD Agent,
Day,
Point,
AutoNumber(Recno(), Agent&Day) as Position
FROM
RecNo.xlsx
(ooxml, embedded labels, table is Лист1);
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()
I 100% agree with Stefan! You can implement the approach suggested by him.
Thanks!
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;
Thanks, Sunny!
Super