Skip to Content

The purpose of this blog is to create a kind of VLOOKUP functionality in HANA  based on real-time data.

This feature will required mainly when you will work  in BOM comparison report.

Like One Material (MATERIAL) may have multiple components (IDNRK) and requirement is one MATERIAL  will cross check with all IDNRK rows (1:N) and if MATERIAL will match with any row of IDNRK then in flag (Calculate column) it will show T else it will show N.

 

Below is step by step process to achieve this functionality in graphical calculation view.

 

 

As per below diagram, trying to explain the requirement.

Layout of the data flow.

 

Step-1

Create 2 calculated columns and concatenate all required column within these two calculated columns based on the requirement.

 

Note:- As per my requirement I used ORD,WERKS SUBID etc. this can be change based on your requirement.

 

Step- 2

 

Pass to Aggregation nodes only pass calculated column CC_MATNR_MATNR and CC_MATNR_IDNRK

Step-3

In next node Join_8, join both calculated column using INNER join

And create a calculated column (CC_FLAG_BOM_IN_COMP_IDNRK) on same node and write below logic.

IF(ISNULL(“CC_MATNR_IDNRK”),‘N’,‘Y’)

 

Step-4

Then pass to next node Projection_1. On that node, now we will have total 3 fields.

  1. CC_MATNR_MATNR
  2. CC_MATNR_IDNRK
  3. CC_FLAG_BOM_IN_COMP_IDNRK

Now we will create another calculated column with below logic

Calculated column- CC_BOM_COMPARE—

if(isnull(“CC_FLAG_BOM_IN_COMP_IDNRK”),‘N’,‘Y’)

 

Step- 5

 

Now in next node -Join_9

We will do joining, J_PLM_NA with Join_9 with left outer join on CC_MATNR_MATNR

then we will create a Calculated column CC_FLAG_BOM_IN_COMP and logic will be – if(isnull(“CC_FLAG_BOM_IN_COMP_IDNRK”),‘N’,‘Y’)

 

 

 

Output

In below diagram Material 62215368 is not matching with any row of IDNRK so in calculates column it is showing  N but when Material 70011280 is searching in all rows of IDNRK then it is matching in  one row so in calculated column its is showing “Y’ for all rows which having material 70011280.

 

it bit difficult to do without using procedure and cursor but requirement was on real time data so finally I decided to create this Kind of VLOOKUP functionality in graphical CV to get data on real time.

 

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Nicolas Piantanida

    Hi Naushad, I made a view with this kind of joins and things to replicate CS11 and CS13 transaction from R3. The problem is lack of memory, because the components are being broken down into cascade way……..one material, many components, and from these components more components asociated and so on. Do you know how to help me? Thanks!

    (0) 
    1. MD NAUSHAD ANSARI Post author

       

      Hi Piantanida,

       

      I would recommend do data comparison in chunk to avoid memory issue.

       

      First try with Material with component then check the result and if its working then go to do with next component with more components and also add all required field in calculated column to make comparison like specification, Plant, etc..

       

      Thanks,

      Naushad

      (0) 
      1. Nicolas Piantanida

        Thanks Naushad for your reply!

         

        I made a comparision in chunk…………First I compare the “first level” of components………then the other, and so on……..but we recieve a memory issue after six or seven levels…… If I make a filter for Material in Semantic process, the view works perfect……….but the client want ALL the materials.

        Regards

        (0) 
        1. MD NAUSHAD ANSARI Post author

          Looks like data volume is high so its creating memory issue error.

          If you are getting memory issue after six or seven levels so I would recommend take a snapshot till six or seven layer then use that snapshot as a table in next  layer using projection. This will improve performance of the CV.

          Note:- Snapshot will make your view non real time.

          (0) 
          1. Nicolas Piantanida

            Sorry, I forgot one thing. I could solve the processing problem by modifying the joins, cardinalities, etc. The inconvenience came later when I want to make the UNION with all the partial results of the levels.

            (0) 

Leave a Reply