Selecting distinct records from a view

[former_member186897]

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

[JTR]

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]

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]

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]

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.