generate gap free data on daily basis based on a table with gaps in dates

[former_member269926]

Hello community, My data model is based on ABAP CDS VIEWS ( Release: Netweaver 751 SP02, HANA database ). This data model is to be consumed by the SAP Analytics Cloud via a live data connection!

Finally I would like to create a daily reporting. However, my data is not complete, i.e. there are no entries on some days. The last value is simply to be transferred here. The following example illustrates my requirement:

Origin Table (source)

I linked the SAP standard calendar (as left table) with the source table via a left (outer) join. At least I get all days:

But how can I update the individual values? The result should be the following table:

With the SQL functions LEAD / LAG I did not really get any further, because I have gaps over several lines and these functions only concern a "neighboring line".

Are there any ideas?

Thank you very much!

Best regards,

Thorsten

[Jean_Sagi]

I really like to to see how this could be accomplished with CDS.
It would be very useful to have this kind of functionality.

J.

[akshinde]

If this is what you want

then i have achieved like this using SQL

Sorry for typo for VLAUE you read as VALUE ; I used windows functions of SQL, not sure if those are available in ABAP as i am more on SQL at HANA Side

SELECT DATEVAL, VLAUE, SUM(VLAUE) OVER ( PARTITION BY RANK ORDER BY RANK) RUNNNG_VAL FROM

(

SELECT *, RANK() OVER ( ORDER BY RUNVAL) RANK FROM

( SELECT * , SUM(VLAUE) OVER ( ORDER BY DATEVAL) RUNVAL

FROM TESTSCNDATE -- REPLACE YOUR TABLE NAME AND COLUMN NAMES HERE

ORDER BY DATEVAL )

ORDER BY DATEVAL

)

[former_member269926]

Thank you very much for your reply! I will try this with ABAP CDS. In the meantime I also found a solution, but yours is looking much better.

I will provide my solution here soon, and I will let you know, if ABAP CDS is working with your suggestion!

Thank you!

Best regards

Thorsten

[former_member269926]

Hello. I tried, but unfortunately my problem is a bit more complicated. I have further dimensions (not only date) like company code, bank and account. I also have another key figure column, which also has to be updated. If someone finds time and pleasure again - can you do this with ANIRUDDHA SHINDE's approach (RANK, PARTITION)? Again a source table and a result table - that you could better understand my challenge. The key of the table will be CalendarDate, Company, Bank and Account.

Source:

Now, all these data on daily basis ( please notice - this is one table - i only put the data next to each other to have a compact view)

And finally, the result ( also one table):

Thank you very much.

Best regards,

Thorsten

[former_member269926]

Hello - here my current solution to the problem described above.

I’ve created a table function with SQL Script, where I select all the transaction data. I also added a column, which is counting the row numbers by Companycode, Bank, Account and date.

This is how the SQL looks like in the method implementation of the tablefunction view:

The Table function CDS view looks like this:

I have also created a view, where I joined my masterdata table with a SAP standard calendar. In the following screenshot this view is called /SERR/I_M_MD_PER_DATE. Important is the ON condition, where I set the <= condition on the date related column, this is for setting previous values to fields, where no values were in the original data.

There is a left outer join between my daily masterdata and my transactional data out of the table function view.

In addition to the already mentioned views I had to create a further view, which returns “Max” values. This view selects on the view above, only the key values with a max aggregation.

This view is needed for the last step – the dimension view. At last step you have to join the "max view" with the "daily-masterdata-transactiondata" view with an inner join. Important again the on condition in regard to the rownumber.

This is how it works, at the moment.

The last inner join is necessary, because the lef-outer-join generates entries, which are not wanted. With the "Max" value it is possible to discard the wrong entries out of the data again.

Best regards,

Thorsten