cancel
Showing results for 
Search instead for 
Did you mean: 

Selecting distinct records from a view

former_member186897
Contributor
0 Kudos

Hi All,

I am wondering how IS does not provide a distinct option when we create a view. Also, it restrict to keep all fields as output column whatever you are using in the joining. Do you know how to take distinct records in data insight without using group by as this is really expensive?

Appreciate your help.

Regards,

Ansari MS

Accepted Solutions (0)

Answers (2)

Answers (2)

JTR
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hello,

I'm also having this problem. I agree with Ansari. I believe it is not possible to solve the problem with the "group by" option. Because whatever columns you select in output schema, in group by it always tells you to add all the columns used.

Let me rephrase try on a differente away.

If you have LFA1 and LFB1 tables, and you want to join information from those 2 tables, you'll have LIFNR (vendor's number) duplicated as they can be expanded to differente company codes.

So, when you build a new view, there's no way you can do that through "Group By". It will displays also the duplicated LIFNR and consequentelly it will duplicate the records when you apply a specific rule to it.

The question is: Is it possible or is there any function in IS which allows us to display distinct records in this case?

Regards,

Former Member
0 Kudos

It will only prompt you to add fields in the output to the Group By, not all fields in the table.  If there are duplicate rows that are only distinct in fields outside of the selected fields, the duplicates will be consolidated (i.e. "select distinct" of selected fields). 

Here's an example joining LFA1 to LFB1 on LIFNR.

View definition:

Output without Group By (duplicates exist):

Output with 3 columns in Group By (distinct values only):

JTR
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hello,

Very nice!

But where do you choose to select distinct values for the first column.

Because in group by it does not let me NOT to select one of the columns of the output schema.

Thank you bery much for your help.

Regards,

Former Member
0 Kudos

The Group By tab has all 3 columns--LFA1.LIFNR, LFA1.NAME, LFB1.LIFNR, they will auto-populate if you click "Propose" on the Group By tab.

This statement

select distinct LFA1.LIFNR, LFA1.NAME, LFB1.LIFNR from LFA1 join LFB1 on LIFNR


would still be selecting distinct values of all 3 columns, not just the first column.  It will produce exactly the same results.

sateesh_kumar1
Active Contributor
0 Kudos

Hi Ansari ,

We can have any columns as output in views.We have options add/remove columns in the output schema .

former_member186897
Contributor
0 Kudos

Sathish Kumar,


My question is simple

1)  There is no way I can select the distinct records in a View. Even if I select limited columns it will return all the rows from table.

2) There is no way you can remove columns from output when it is used in your join.

Do you know any alternate for this in IS?

sateesh_kumar1
Active Contributor
0 Kudos

Hi Ansari ,

Mostly in IS we do identifying duplicates . There won't be any use to restrict duplicates in views.

I can able to remove columns which are used for joining from output schema by selecting row and remove.

former_member186897
Contributor
0 Kudos

It depends if we want to get rid of Cartesian product while joining multiple tables. Let me try again with removing columns from output however I had tried earlier and it is not working. Will try with some other table and see if that does work.

Former Member
0 Kudos

If you want distinct records, you can put all the selected columns on the "Group By" tab--just hit the "Propose Columns" tab.

You will have to unbind any rules bound to a column you want to remove and also remove that view from any tasks before you can remove the column.  An alternative solution would be to keep the column name in the view but re-map it to a static value of 0 or null or something (so it does not reference the original table) in your view.