Help with query grouping

Ewanm

I am running a query which returns week number stored in a BO. I want to group the output into single week numbers but only want to display one instance of the week number for selection.

eg My Output looks like this

24
24
24
26
26
28

The output I am looking for is

24
26
28

Can anyone hep me with the query structure to achieve this?

JonP

Unfortunately, AIM lacks the equivalent to SELECT DISTINCT. You'll either need to create a stored procedure or create a BO and populate it with distinct values.

Ewanm

I'll try creating the BO.

Thanks for the reply.

PointsWell

You don't need to create a BO. In the query settings there is Grouping.

It will only let you group by one level. If you want multiple levels there is a How To with JS that Hendrik posted in the Tips and Tricks

Ewanm

Thanks PointsWell , I have already tried that but although it groups, it then shows all the instances of the week number under the group, I simply want to display one instance of each and slow me to select that week to process further.

Thanks though.

PointsWell

JonP’s Stored Procedure then or a separate BO as a reference object for the week number rather than an integer attribute.

Ewanm

Don't quite understand what you mean by "separate BO as a reference object for the week number rather than an integer attribute", can you point me in the right direction.

PointsWell

Im going to assume that you are working on an order management system as context.

Orders has an attribute WeekNo which is a peer single relationship to Weeks which has a peer multiple to Orders

You will have 52 Instances of Weeks and your query would be find Weeks where orders in weeks.pmOrders>0 [this is obviously not the correct syntax but it’s late in Aus and I’m not in front of a computer]

You then have distinct Weeks and you could expand the row to show further details or attach a process to the Weeks BO row in the query.

Generally when I am building BOs I use an external BO for reference data and don’t use options in text fields - it makes updates to reference data easier and allows for slowly changing dimensions. Obviously there’s always 52 weeks in a year but it overcomes your challenge.

ACDC

Orders has an attribute WeekNo

I think you would also need to cater for YEAR .. just saying :idea:

Jaymer

Or if you have SQL back end, this is a simple no brainer.