SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing
+
-
Predictive Analysis

Text Join


Text Join is used in order to get language-specific data.

Before understanding the text join, it is important that we understand Text Tables.

Text Tables:

Table A is a text table of table B if the key of A comprises the key of B and an additional language key field (field of data type LANG). Table A may therefore contain explanatory text in several languages for each key entry of B.
SAP HANA Text Table

Text Join in SAP HANA:

Text Join is used to fetch the description based on user's session language. Once we implement the text join in SAP HANA, it automatically find out user's language and give description in that language.

Implementation of Text Join in SAP HANA:

Example:
Suppose there is a table called "PRODUCT" which contains Product ID and Product Name.
There is also a text table of "PRODUCT", which is called "PRODUCT_TEXT_TABLE". The text table contains the description of products in different language. For example "Cotton Shirts" in English and "Baumwoll-Shirts" in German.

Purpose:
Create a calculation view which will give Product Name and Product Description. The Product Description should be only in user's session language.

Implementation:
    1. Open HANA Studio. Right click on your schema and open SQL Editor. Copy the below SQL script to create 2 tables - "PRODUCT" and "PRODUCT_TEXT_TABLE".

      -- REPLACE WITH YOUR ACTUAL SCHEMA NAME

      -- PRODUCT table
      CREATE COLUMN TABLE ""."PRODUCT"(
                  "PRODUCT_ID" INTEGER ,
                  "PRODUCT_NAME" VARCHAR(20) ,
                  primary key ("PRODUCT_ID"));

      -- PRODUCT text table
      CREATE COLUMN TABLE ""."PRODUCT_TEXT_TABLE"(
                  "PRODUCT_ID" INTEGER ,
                  "LANGUAGE" VARCHAR(1),
                  "PRODUCT_DESCRIPTION" VARCHAR(50) ,
                  primary key ("PRODUCT_ID", "LANGUAGE"));

      insert into ""."PRODUCT" values(1,'Shirts');
      insert into ""."PRODUCT" values(2,'Jackets');
      insert into ""."PRODUCT" values(3,'Trousers');
      insert into ""."PRODUCT" values(4,'Coats');
      insert into ""."PRODUCT" values(5,'Purse');

      insert into ""."PRODUCT_TEXT_TABLE" values(1,'E', 'Cotton Shirts');
      insert into ""."PRODUCT_TEXT_TABLE" values(1,'D', 'Baumwoll-Shirts');
      insert into ""."PRODUCT_TEXT_TABLE" values(2,'E', 'Leather jacket');
      insert into ""."PRODUCT_TEXT_TABLE" values(2,'D', 'Lederjacke');
      insert into ""."PRODUCT_TEXT_TABLE" values(3,'E', 'Trousers and Pants');
      insert into ""."PRODUCT_TEXT_TABLE" values(3,'D', 'Hosen und Hosen');
      insert into ""."PRODUCT_TEXT_TABLE" values(4,'E', 'Coats and Blazers');
      insert into ""."PRODUCT_TEXT_TABLE" values(4,'D', 'Muntel und Jacken');
      insert into ""."PRODUCT_TEXT_TABLE" values(5,'E', 'Purse and Handbags');
      insert into ""."PRODUCT_TEXT_TABLE" values(5,'D', 'Geldburse und Handtaschen');


      Note: The Language code for English is "E" and for German is "D"
    2. Right click on your schema and refresh to see the 2 tables created.

      SAP HANA Text Join
    3. Go to "Content" and right click on your package. Select Calculation view.

      SAP HANA Text Join
    4. Give Name and Description of the Calculation View. Select Data Category as "Dimension".

      SAP HANA Text Join
    5. Add a "Join" block and add both the tables "PRODUCT" and "PRODUCT_TEXT_TABLE".

      SAP HANA Text Join
    6. Make a join on the column "PRODUCT_ID". Specify the Join Type as "Text Join". Click on the Language option and select "Language" column. Add "PRODUCT_ID", "PRODUCT_NAME" and "PRODUCT_DESCRIPTION" to output.

      SAP HANA Text Join
    7. Connect the Join block to Projection Block. Add all the 3 column to output.

      SAP HANA Text Join
    8. Select the Semantics block. Select the Type of Columns as "Attribute". This might be selected by default. Save and Activate the view.

      SAP HANA Text Join
    9. Click on the Data Preview to see output of the calculation view.
      The output contains only the English Description.

      SAP HANA Text Join


Change User Session Language (Locale) and Check The Output Again:


    1. Right click on the HANA system and select "Properties".

      SAP HANA Text Join
    2. Click on the tab "Additional Properties" and select the Locale as "German"

      SAP HANA Text Join
      Click on "OK".
    3. Check the output of the calculation view. Now the output contains German Description.

      SAP HANA Text Join

Support us by sharing this article.



Explore More
Close X
Close X

11 thoughts on “SAP HANA Text Join

  1. ram says:

    Hi,

    E represents English, D represents German Like this where can we get the list of all Language codes?

    • Admin says:

      Hi Ram,
      Please refer to the link: http://www.se63.info/sap-language-code-list/

      Regards,
      Admin

    • Kevin says:

      Hi There,

      Just the information I was looking for. Thanks for the detailed instructions. I haven’t used it yet but I guess the time has come.

      My question is basically the outcome of the hash partitioning test with an integer column. I have raised a question on the hash function: how it generates hash number on an integer column. Below is the link to the ticket.
      We want to override hash function according to our need so that it will suit out business need SAP HANA Training . Is there any way we can override the hash function?
      By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
      Please keep providing such valuable information.

      Kind Regards,
      Kevin

  2. Fabian Szymczak says:

    Hi,

    thanks for this helpful advice.

    In my case I have defined the extra column “masterLanguage” in table PRODUCT. So if the session language is not available in the “Language” column, the text join should use the “masterLanguage”, which is for example E.

    My problem is, I am not able to figure out where I can make this setting. Maybe you have another advice for me?

    Regards,
    Fabian

    • Admin says:

      Hi Fabian,
      In case the session language specific entry is not there in the table, text join will pick the entry belonging to default language English.
      You can just make sure that atleast english language description is available.

  3. Karthik says:

    Can you please the default join type of text join.
    Suppose if there is no matching record in text table for the master data table, then left join will apply to that or it will apply inner join ?

  4. Palani Ramu says:

    super explanation, Variables / input parameter for analytics view of SAP HANA

  5. summer smth says:

    text me alert via my cel# +639192616111
    and text me how to join

  6. Rahul says:

    Greetings Mate,
    THANKS SO MUCH for sharing this! I would love to buy you a coffee since I now won’t be up all night that has been driving me crazy (until now!!).

    I have to create a table if it doesn’t exist.
    I have gone through this answer https://archive.sap.com/discussions/thread/3383646 but here we use procedures and in it we are having a count of number of rows if greater than zero then the required execution is being made. But in my case the table is empty, even if it is created, so how could I check for it to be present in the DB? Is there any alternative way of creating a table (if it doesn’t exists) other than the procedure check?

    Excellent tutorials – very easy to understand with all the details. I hope you will continue to provide more such tutorials.
    Obrigado,
    Rahul

  7. Phani says:

    Greetings Mate,

    Thank You so much for this blog. It helped me lot. I am a Technical Recruiter by profession and first time working on this technology was bit tough for me, this article really helped me a lot to understand the details to get started with.

    I have written a XSJS to call a SOAP Service and with the response, update a CDS table. XSJS is working as expected.
    But when I configure the XSJob to call this XSJS, I am getting an error “Error: XSDS: register: duplicate entity”
    Anyone has any suggestions? Or did someone faced this before?

    Appreciate your effort for making such useful blogs and helping the community.

    Obrigado,
    Phani

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy