Filling empty fields

[former_member237224]

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

[former_member199842]

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

[former_member237224]

Hi,

Thank you very much for the help!

I tried the way Suresh explained but I have problems. Here are the steps which I make

1. Create a Calc View (Category: Dimension)

2. Insert a projection node

3. Insert a table and make columns visible. Then SAVE

4. Try to display data but there is nothig to display. So close the View and open it again. Then, as you can see, the table is outside the projection

What am I doing wrong?

Thank you very much!!

[former_member199842]

Hi Mirza,

After step 3 , did you add the output columns from the bottom most projection as the output of the view/ output of the default projection node.

I mean , you must see the output columns in the semantic node.

Then activate the view(Not just Save) and perform the data preview.

Thanks and Regards,

Poorna

[former_member237224]

Hi,

Sorry of course I activate and do not save only.

At the step 3 I do following.

- Add the colums to output (bottom projection)

- click on the fields I want to display (PARENT_KEY and VOYAGE_ID)

- in semantics I see the fields

-> ACTIVATE

But again no data.

Close and open view and the table is again outside of the bottom projection

[former_member237224]

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.

[Former Member]

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

[former_member237224]

Hi Nihal,

Thank you very much.

Is it possible to achieve this only with graphical views?

The Column "Language" was only a example. Instead of language we have a column with thousand of different values. It is impossible to make a 'case' on it

I tried it with a join of a Attribute view with only 2 columns (ParentKey, Language) where language is filled and a Calculation View. The attribute view would act like a lookup table. Unfortunately it does not work or I am not able to do it

[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.