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.
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.
Pass to Aggregation nodes only pass calculated column CC_MATNR_MATNR and CC_MATNR_IDNRK
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.
Then pass to next node Projection_1. On that node, now we will have total 3 fields.
Now we will create another calculated column with below logic
Calculated column- CC_BOM_COMPARE—
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’)
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.