VLOOKUP in HANA
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.
- CC_MATNR_MATNR
- CC_MATNR_IDNRK
- 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.
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!
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
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
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.
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.