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?
Help with query grouping
Re: Help with query grouping
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.
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
Re: Help with query grouping
I'll try creating the BO.
Thanks for the reply.
Thanks for the reply.
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Help with query grouping
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
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
- Attachments
-
- Screen Shot 2018-11-17 at 10.48.34.png (19.82 KiB) Viewed 11983 times
Re: Help with query grouping
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.
Thanks though.
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Help with query grouping
JonP’s Stored Procedure then or a separate BO as a reference object for the week number rather than an integer attribute.
Re: Help with query grouping
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.
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Help with query grouping
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.
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.
Re: Help with query grouping
I think you would also need to cater for YEAR .. just sayingOrders has an attribute WeekNo
Re: Help with query grouping
Or if you have SQL back end, this is a simple no brainer.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL