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.

Screen Shot 2018-11-17 at 10.46.28.png (37.54 KiB) Viewed 11905 times

Screen Shot 2018-11-17 at 10.46.28.png (37.54 KiB) Viewed 11905 times

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

[Jaymer]

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