Hello all,
I have a requirement where we need to get the value of last row's ending balance as beginning balance of the current row and this process should carry on for next 100 rows. And we don't have any base table for this and everything is build from the Custom table.
Currently, we are creating multiple tables for references, but ended up having GBs of data in Cube.
So, is there any possible solution for this challenge?
Thanks,
Aditya
Hi Aditya,
possible solution to this is as follows:
1. Rank your main table by one or combination of columns to get Ranks 1,2,3 etc.
2.Create Custom SQL expression in which you can JOIN that main table to itself on the RANK column you just created but with offset:
SELECT * FROM TABLE A
JOIN TABLE A
ON A.RANK = A.RANK-1
This way you create a table where every row will have access to information from previous row. Of course adjust the query to your liking and don't necessary use SELECT * 🙂 Hope it helps!
Regards,
Michael Becker
qbeeq.pl
Hello Michael,
Thanks for the response.
Currently, we implemented the similar approach to our use case. For this to work, we have to create multiple table relating to each other and union all in the end to make the resulting table. Also we don't have any base table for this, even the initial row is being created as a custom query. Just checking if there's any alternative solution so we can make up the data into a single table instead of having every row being referred to the previous table and ending up huge number of tables.
Thanks,
Aditya
