Skip to Content
avatar image
Former Member

Filling empty fields

Hi all,

Here is the scenario that we have in a table:


Key ParentKey Language Number co1 P001 EN 100 co2 P002 DE 120 co3 P003 FR 60 co4 P001 80 co5 P001 60 co6 P003 100


I need a View, if possible grapgical, to fill the empry language fields

Result should be



Key ParentKey Language Number co1 P001 EN 100 co2 P002 DE 120 co3 P003 FR 60 co4 P001 EN 80 co5 P001 EN 60 co6 P003 FR 100

Thank you

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 20, 2016 at 08:50 AM

    Hi Mirza,

    Above scenario can be achieved using two calculation views. One view to provide the look up values for LANG for each of the ParentKey and another to fill the LANG column for rest of the ParentKey by using the look up value from the latter Calculation view.

    Let us take the same example which you have mentioned above and create a column table first  as shown below :

    CREATE COLUMN TABLE HE2E_USER."SCN_1" ("KEY" VARCHAR(1000),

      "PK" VARCHAR(1000),

      "LANG" VARCHAR(1000),

      "NUMBER" INTEGER CS_INT)

    and insert the values accordingly :

    insert into "HE2E_USER"."SCN_1" values('col1','P001','EN',100)

    ;

    insert into "HE2E_USER"."SCN_1" values('col2','P002','DE',120)

    ;

    insert into "HE2E_USER"."SCN_1" values('col3','P003','FR',60)

    ;

    insert into "HE2E_USER"."SCN_1" values('col4','P001','?',80)

    ;

    insert into "HE2E_USER"."SCN_1" values('col5','P001','?',60)

    ;

    insert into "HE2E_USER"."SCN_1" values('col6','P003','?',100)

    Step1 : Let us first create the base CV which can be used for look up purpose.

    Create a new CV of DIM type and add another projection node to the existing default projection node and insert the above created column table to the added projection node and select PK and LANG columns as the output columns of the node as is shown below :

    Step2 : Now Go to filters section and create a filter for LANG column using the expression :

    (not isNull("LANG"))

    as is show in the below image :

    Step 3: Now add the 2 columns to the semantics and activate the CV and check for its data preview.

    It must be similar to the below result set :

    Now we have our look up value ready.

    Step 4: Create another CV of Dimension type now  and add a join node the view.

    To the join node add the above created CV and the base table as is shown below and join on the PK column. Once done , add KEY, PK and  NUMBER column from the base table and LANG column from the above created view as the set of output columns as is shown below

    Step5 : Once done, add the same columns to the semantic and preview the view by activating it.

    You must now get the desired result set that fills the rest of the values in LANG column as is shown below :

    Hope you find it useful.

    Thanks and Regards,

    Poorna


    SCN_DIM.PNG (53.0 kB)
    Filter.PNG (32.9 kB)
    DP.PNG (7.2 kB)
    Join.PNG (55.8 kB)
    Final_DP.PNG (16.4 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member poorna Malamandi Suresh

      Hi,

      Thank you very much. It works now!

      But I had to create a Attr.View, a Analytic and a Calc.View to get it to work.

  • avatar image
    Former Member
    Jun 20, 2016 at 07:40 AM

    Hello Mirza,

    Have you tried using a "calculated column" for this use case (using an if condition for filling up entries).

    In a script based CV, you could rely on the "case" statements to fill this up.

    Kindly refer to the Modeling guide for SAP HANA for the exact syntax.

    Regards,

    Nihal

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Mirza,

      Well if it such intensive logic, i would recommend you re-think of the approach to persist the values while bringing the data in itself in the table.

      Perhaps a rule written via SLT or any other tool which you're using thereby making even the redundant computation faster while modeling.

      Calculated column is the way to go if it's a graphical based. If you're able to identify the combination, you can rely on the various functions in the calculated column and achieve the desired result.