on 07-29-2014 10:36 PM
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
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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):
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.
Hi Ansari ,
We can have any columns as output in views.We have options add/remove columns in the output schema .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.