cancel
Showing results for 
Search instead for 
Did you mean: 

How to get distinct values from table in calculation view?

Former Member
0 Kudos
4,297

Hi ,

I have a scenario in which there are two tables.

1. MPM_PROD_EVENTS

2. MPM_EVENT_MAP

MPM_PROD_EVENTS has many events with its effective durations.

MPM_EVENT_MAP contains Parent and child Events associations.

Suppose In MAP table I have association as

Parent Event          Child Event

51                              10

51                              11

MPM_EVENT table has entries as:

Event ID          Duration

51                    100

Now my requirement is that I want only those events from EVENT table which are present as Parent_Event in MAP table.

On doing left join between Map and Event table ,

I am getting Records as :

Parent ID  Duration

51               100

51               100

But Duration of 51 is 100 only.On aggregating this , It will make duration of event 51 as 200.

To solve this problem I need to get distinct values of parent id from MAP tables.

Same example is there in tmp.oee.test/test_events in VS3.

Please check and reply.

Thanks and Regards,

Tarun Rawal

Accepted Solutions (1)

Accepted Solutions (1)

sreehari_vpillai
Active Contributor
0 Kudos
Raja_Gupta
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Sreehari,

The distinct operation in not available in modeling view. That's pretty clear. But how it can be achieved using self join?

Could you please elaborate.

I will simplify the problem.

CREATE COLUMN TABLE T1(

     ID INTEGER

);

CREATE COLUMN TABLE T2(

     ID INTEGER ,

     NAME VARCHAR(10),

     VALUE DOUBLE

);

INSERT INTO T1 VALUES (1);

INSERT INTO T1 VALUES (1);

INSERT INTO T1 VALUES (2);

INSERT INTO T2 VALUES (1, 'A', 100);

INSERT INTO T2 VALUES (2, 'B', 200);

INSERT INTO T2 VALUES (2, 'B', 100);

INSERT INTO T2 VALUES (3, 'C', 300);

Target is to implement similar SQL query in calculation view.

SELECT NAME, SUM(VALUE)

       FROM T2 AS T2 INNER JOIN

       (SELECT DISTINCT ID FROM T1) AS T1

          ON

       T2.ID = T1.ID

GROUP BY T2.NAME

former_member182114
Active Contributor
0 Kudos

Hi Raja,

Use aggregation to work as distinct:

1) Model one aggregation node for T1 add ID only as output

2) Model a join with T2 and aggregation

3) Model another aggregation with result of join with name as output and value as measure

Regards, Fernando Da Rós

Former Member
0 Kudos

Hi Fernando ,

Thanks for your reply.

Using Aggregation block instead of Projection block for ID of T1  worked and fetches distinct values of ID's.

Thank you very much.

Thanks and Regards,

Tarun Rawal

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

Hi Khalid,

In your scenario,

It looks like you have detailed table and summary table, however there is one column that makes the detail table as detail for example:

summary Table = ST   , Detail Table = DT

ST                                      

Prd_Id                   total cost

1234                       $150 

DT

Prod_ID               pro_Detail            Cost_per_D_Prod

1234                     Pro_1                    $100
 

1234                     Pro_2                     $50

This is I suppose your structure in this case it's either when you join the 2 tables don't bring the column that gives details just bring all other columns and you should be fine,

if you have to have the details about product you will need to create another aggregation where you use the detail_Pro and give it different name than the other measure.

hope this answer your question.

Answers (0)