Passing values derived from one table to get value...

Hi All,

I created Analytical view on VBAK table. I applied some filters and dervied few sales orders. Now I want to pass these sales orders to VBAP table and derive few records and again applying few more filters on VBAP.

How do I achieve this?

Thanks

Srikanth

SAP Managed Tags:
SAP HANA

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

Hi sumeet,

It's not wise to change your table field data type. It can be used elsewhere right. So create a generated column. Formula will be something like to_integer("column").

Regards,

Safiyu

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Safi,

Thanks a lot for your suggestion. I am developing model based on FS shared by Sri BW.

I am facing one major performance issue.I used calculation view for half of the development and created one more calculation view and used the previous one into the current calculation view in join.

And then starting joining with some more joins as per FS on different tables like LIKP and LIPS.

For first calculation view model I am getting the output but when I am putting the same in another calculation view its giving below error:

SAP DBTech JDBC: [2048]: column store error: search table error: [9] Memory allocation failed

Do I need to restructure my model by using attribute view and then using the same in analytic view?

Sumeet

Hi Sumeet,

I couldnt clearly understand. But one suggestion from my side, Keep master data's in attribute view. Then for star join use analytical and only if there are complex calculations keep calculation view. Go with this order.

Regards,

Safiyu

Hi safi,

Thanks for your reply. yes I got your point. Actually i am just trying through calculation view also. What i did I created one calculation view and did half part of development in calculation view say CALC1 and then created one more calculation view say CALC2 and called CALC1 in CALC2 in one of the join in CALC2 but when I did data preview its giving above error.

Is it not recomended to call one calc view in another?

Then what I did tried everything in one calc view but still it takes lot of time.

Sumeet

Hi Sumeet,

No you can use calc view in another calc view. For your error you can try these.. Reduce data of the tables which are joining. memory allocation failure may be because of large data. Try setting filters at lower level.

Regards,

Safiyu

Hi,

Thanks for reply.

sorry what do you mean by setting filters at lower level? I am putting the same at projection level for which i need to filter the value.

Thanks and regards

Sumeet

Hi sumeet,

At data foundation level.

Regards,

Safiyu

Hi Sri,

Didnt get your question completly. But as per my understanding, you have to join sales order with vbap. Then again apply filters on it. If i am wrong, can you elaborate your requirement.

Regards,

Safiyu

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Saif,

The following is my requirement

1. Select sale orders from VBAK where doc category is "D" for the selected date in the variable.

2. Pass all the sales orders to VBAP where rejection is <> blank and delievery status = A from VBUP

The above is just the one step in the whole set of requirement. i will go one by one.

Hope its clear.

Thanks

Sri

Hi,

"Pass all the sales orders to VBAP" . What do you mean by pass?

Regards,

Safiyu

Hi,

Created Analytical view on VBAK table with filters defined in semantics.

Say filter is document category = C for date 20150110

In data preview i got 12 records. Now I want this 12 records to be joined with VBAP and VBUP and get values for only those records. Further more I need to put filter in VBAP where rejection is <>blank. So from 12 I may get only 10 records.

Hope its clear.

Thanks

Sri

Hi Sri,

Yes got it.

You can do this in a calculation view as shown in this image

Take vbap(projection 1) and vbup(projection 2) in one join. And in projection2 of vbup, apply filter for rejection from right click context menu. then take filtered vbak in projection 3. voila...

Note: You dont have to create a vbak analytical view. Directly you can put vbak table in projection 3 and apply filter in projection itself..

Hope it helps..

Cheers,

Safiyu

Hi Saif,

Here I am unable to understand logic.

I want to select sales orders from VBAK based on date, sales org , SD Document category and company code input parameter restriction. After getting sales orders i want to do inner join (I think) to get the sales order from other two tables.

Now what you have proposed i dont think i can do that in projection 3 and pass to other projections. IS my understanding right on your proposal?

Thanks

Sri

Hi Sri,

In projection 3 you select the req fields and then apply filter on those fields.. Similar to the analytical view you have created. if your analytical view consists of two or more tables then put the analytical view itself in projection 3.

Inner join of projection 1 and 2 with filers in projection 2 will give u the required output from vbap and vbup. and then inner join it with vbak table in projection 3 with filters to get the req output.

Did you try doing it in this way? Issues yet?

Regards,

Safiyu

Hi Saif,

Thanks for your help. The above mentioned is only one part of the requirement but there are other things as well in the specs. Would you mind sharing your email id so that I can send it to you. Please let me know if its ok.

Thanks

Sri

Hi,

Yeah Sure,

Safiyu@icloud.com

Regards,

Safiyu

Hi Safi,

I just tried it with analytical view also and achieved the same.

I added all the tables in data foundation and applied filters and joined the same.

And got the result.

Kindly let me know if my approach is correct or not.

And how can we measure the performance of any model which we are building?

Since i can see we can build requirement with different ways just like we see now.

Thanks in advance

Sumeet

Hi Sumeet,

Yes you can achieve it in different way. Analytical views are meant to be used to achieve star schema. Here you dont have any objects in your logical join node right. I am not so sure about this way of achieving it. . On the other side, Analytical view Joins faster. So this may be the best option. Should cross check on it. Anyways thanks for sharing it. And yeah ofcourse if you have objects in logical join, this is the right way.

Cheers,

Safiyu

Hi,

Yes you are right. I think instead of analytical view I have achieved the same with the help of attribute view also. same I added all the tables in Data foundation and applied filters and joined the same.

Thanks in advance

Sumeet

Hi sumeet,

True. But what will you do for transaction data. How will you use it in attribute view.

Regards,

Safiyu

Hi Saif,

Send you the mail.

Thanks for your help.

REgards

Sri

Hi Sri,

I went through the doc. I'm implementing the similar scenario in my project.

For all master data level joins, join it in attribute views. And for transaction level, join these attributes and the corresponding fact table in analytical view. You can also join two or more fact tables in data foundation node. And to apply filters, right click on relevant column in data foundation and apply filter.

Let me know if you require further help on this.

Regards,

Safiyu

Hi Saif,

Thanks for going thru doc.

I completed first scenario VBAK, VBAP and VBUP with your help.

Now my issue is for the second scenario

Pass
all VBAK-VBELN to VBAP
Pass
all the VBAP-VBELN and VBAP-POSNR to VBFA-VBELV & VBFA-POSNV and get all
the VBFA-VBELN and VBFA-POSNN where VBFA_VBTYP_N = J and M

Following is my first modelling

Now how to work with the second and other approaches. Can you help me in giving idea how to pass, join different restrictions and combine all together.

Thanks

Sri

Hi Sri,

Since you have many similar requirements, You can achieve these in analytical views as shown in diagram.

Choose the fields required from each table. It will get passed on to next table. And make sure to add the column on which you are joining only from a central table. ie, Here i have considered vbpa as central table. So chose vbeln from it on which we were joining. And wherever filters are required apply filter from the menu as shown in the picture.

Hope this helps.

Regards,

Safiyu

Thanks a ton saif. I will try this and will update you.

REgards

Sri

Hi Sri,

If you dont mind can you please share your req. with me also?

ID: <personal email removed by moderator>

Sumeet

Done Sumeet

Hi Sri,

Sure. Let me Know once it is done. Also make sure of the join conditions and cardinality for proper results.

Cheers,

Safiyu

Hi Saif,

I completed part a.

Now how to i join VBAP with VBFA in Part B

Pass

all the VBAP-VBELN and VBAP-POSNR to VBFA-VBELV & VBFA-POSNV and get all

the VBFA-VBELN and VBFA-POSNN where VBFA_VBTYP_N = J and M

Following is my part A.

Now how to join VBFA??

Thanks

Sri

Hi Sri,

Create another analytical view and follow the similar steps. Take vbap and vbfa. Join it and apply filters. Perform similar steps for your requirements. If the requirement is to add vbfa to the same view, you can do that. vbap will act as central table.

Regards,

Safiyu

Hi safi,

i have joined couple of tables in data foundation but i can see datatypes cannot matches for some fields like POSNR and POSNV.

What needs to be done in this case? is there by anything we can change the datatype?

And same error I got while joing VBAP-POSNR and VBUP-POSNR. Both have different data types.

Error: "SAP_SLT".VBUP.POSNR(NVARCHAR)_"HANA".VBFA.POSNV(DECIMAL) are incompatible in Data Foundation node

Sumeet

Hi Sumeet,

In that case you have to create a generated column in your source table. Which basically typecast your column to required data type. Here is syntax to create one.

ALTER TABLE "<your_schema>"."<your_dimension_table>" add ( <calculated_attribute_name> varchar GENERATED ALWAYS AS (<formula>));

Regards,

Safiyu

Hi Safi,

I am trying to alter with below syntax to convert datatype of POSNR from NVARCHAR to DECIMAL but its not allowing me to do the same. Why its not allowing me to do the same?

ALTER TABLE "SAP_SLT"."VBUP"

alter("POSNR" DECIMAL);

SAP DBTech JDBC: [7] (at 36): feature not supported: cannot modify column type from nvarchar to decimal: POSNR: line 2 col 7 (at pos 36)

Sumeet

Also one more thing it will not allow us to change original datatype? Are we going to create new column say POSNR_1?

Hi sumeet,

Yeah you need to create a new generated column. ie posnr_1 . Also ALTER does not currently follow data type conversion rules. that is why you are not able to change the data type

Regards,

Safiyu

Hi Safi,

I did not get what needs to be passed in formula thing since I need to modify only datatype of POSNR from varchar to decimal. is that possible with any query? Or only option is to add to new column POSNR_1 only of datatype DECIMAL?

Sumeet

Ask a Question

Top Q&A Solution Author

User

Count