keeping only the first row of a certain ID number in a repor

Hello,
I have created a report similar to the link. (excel table similar to my report)

My problem is the redundancy in this report. I need only the very first row (the yellow lines) for each ID (The ID is “Kunde ID”) .
Is it possible to group them in a way? The filter function does not work since there is no identification for the first row. I tried for hours but I am simply to inexperienced

I apologize if a similar question was already posted but I am completely new to this topic.

MarionW


marion_wieland2000 (BOB member since 2004-06-20)

What’s the reason for the first row? Is there something about the number you need? Is it really the FIRST row or ANY row?

Normally people would want to add up the second column. Or at least do something with it (average, min, max…) To do this, you need to properly define the second column as a measure in the universe AND put aggregation on the SQL. This will cause the query to generate a GROUP BY clause and give you what you want.


Steve Krandel :us: (BOB member since 2002-06-25)

Thank you Steve,
but I am still not sure what to do,
I need only the first row cause I sorted the report before that in a way that all the information to a certain ID are in that first row.
The second column is a note regarding the ID.

How can I put aggregation on the SQL?

I think this Group by clause is exactly what I need!

MarionW


marion_wieland2000 (BOB member since 2004-06-20)

What are you sorting by? What is causing the order of the data? There must be some other column. You likely need something at the report level that will handle the sorting.

It seems like a Min or Max or Average applied to the 2nd column is what is needed, but that won’t work exactly correct. There must be another column that is causing you to want the 1st record. Is there a date or …


Steve Krandel :us: (BOB member since 2002-06-25)

Just an idea:
See if you can incorporate all the logic instead in a subquery to retrieve only one row per Customer ID (Kunde ID).

Do you want to sum up the Revenue Amount (Umsatz) for each Customer ID?


Andreas :de: (BOB member since 2002-06-20)

Thank you,

Steve: Im sorting for the type of note as explained in the jpg link.
The problem is that I want:

  1. only one line per ID (no double or triple) lines per ID
  2. If there is a note regarding an ID with ‘Type of note’= 'general Info’it should be includet. If i am filtering for generl info I miss the IDs that have notes but not with the topic about general info.

So i sorted for that general info that it is stated in the first line of a ID.
Now I need to delete the 2.,3.,4… lines.

Andreas> I need oly the report in the link. i do not need any aggregation.
Thank you in advance
Marion


marion_wieland2000 (BOB member since 2004-06-20)

What’s causing you the problem is the definition of “First”. By definition, the order of rows in the database doesn’t matter. So, again, what causes a row to be the first row?

To be honest, I don’t think you can do what you want at the report level. I’ve thought about outlining and a bunch of other methods, but nothing will get you what you want.

I can easily get you the General Information rows. The problem in in how you get the “first” row when all 3 rows are really equivalent and have the same description.


Steve Krandel :us: (BOB member since 2002-06-25)

I guess it can not be done in that way. Maybe I’m on the wrong track using a sort.
But I would get the desired result if I could merge 2 query.
I created a new topic cause I think I confused you in this topic to much using different screenshots.

It would be really a great help for me If a merge would be possible.

Marion


marion_wieland2000 (BOB member since 2004-06-20)