Is there an ETL function for counting row?

The Hackett Group

Hi,

I've got a cube that requires a line reference number as a dimension (from the load table) - I'd like to add a row counter to the ETL in the data reader where this cube is populated, but I'm not sure what the function would be (or if one exists).

Does anyone know what the function is or a workaround?

Thanks!

Shisong

As another work around, you can use smart import instead of data reader. In smart import object, there's an option to set rules as "Auto incremental".

The Hackett Group

https://community.board.com/discussion/comment/17706#Comment_17706

That's a brilliant suggestion, thanks!

I was planning on adding a Smart Import in a later build so this works perfectly.

Tommaso Riva

@The Hackett Group have you tried exploiting the ROW(reference)function?

This function returns the row number of the supplied reference.

The Hackett Group

https://community.board.com/discussion/comment/17689#Comment_17689

Hi Tommaso, thanks for your reply!

I was not aware of the function - do you know what I would use as a reference?

Any cell reference I try returns the row number in the ETL, not the row number of the table (C25 = 25 for example).

Tommaso Riva

@The Hackett Group You could option for a dynamic reference by using a nested function.

In general, It is always reliable to have the row number already provided in the source table.

Filip Rankovic

Hi @The Hackett Group

In case you are using a SQL DataReader and depending on the desired use case, the SQL row_number() function might do the trick:

select row_number() over (order by {column to count} desc) as index_new_name, {other desired fields}
from table

Pleas, let me know if that helped.

Best regards,
Filip

————————————-
FIlip Rankovic
Consultant
Board Deutschland GmbH

Nitish Subramanian

https://community.board.com/discussion/comment/17693#Comment_17693

Hi Tomaso,

If you don't mind, can you please share how nested functions can be used in ETL to determine row counts?

Tommaso Riva

Hi @Nitish Subramanian,

to know the number of rows, you need to place some temporary tiles to get there.

For example, add a rank to establish a row number column, then aggregate with a group to get the maximum row.