Is there an ETL function for counting row?
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!
Accepted Answers
-
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".
0 -
That's a brilliant suggestion, thanks!
I was planning on adding a Smart Import in a later build so this works perfectly.
0
Answers
-
@The Hackett Group have you tried exploiting the
ROW(reference)function?This function returns the row number of the supplied reference.
0 -
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).
0 -
@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.
0 -
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 tablePleas, let me know if that helped.
Best regards,
Filip————————————-
FIlip Rankovic
Consultant
Board Deutschland GmbH0 -
Hi Tomaso,
If you don't mind, can you please share how nested functions can be used in ETL to determine row counts?
1 -
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.
0
