69
Views
24
Comments
Aggregate Group By with Attribute failed to show the records
Question

Hello everyone, I want to create a group by aggregate from an attribute with Date data type. Instead, I getting this result:

Does everyone know what causing this? Thanks.

mvp_badge
MVP
Rank: #128

Aditya Eka Prabowo wrote:

Hello everyone, I want to create a group by aggregate from an attribute with Date data type. Instead, I getting this result:

Does everyone know what causing this? Thanks.


Hi,


I think your RequestDate columns are DateTime?

If then first, you need to use the function to get the date only and then use the group by.

mvp_badge
MVP
Rank: #128

Hi,


Please below snape for reference,


mvp_badge
MVP
Rank: #128

I don't think it should work with no need to create an entity again.

You can use advanced SQL to resolve this issue.

I think this is an issue.. even now I've changed the attribute itself to "Date" type only, it still didn't work:



Is this a bug regarding my environment or service studio?

Rank: #89

Hi Aditya,

Can you share oml with us?


Regards

Rahul Sahu

Hi Rahul,


Here's the OML. The screen name is "MyRequest" and "WorkList". 

Thanks before

MoanaMobileApplication.oml

Rank: #89

Hi Aditya,


Carlos provide solution in oml 

hope this will help you.


Regards

Rahul Sahu

Hi Carlos, yes I've tried that before.

I've already created a dummy attribute to convert and group by that. But here is the result instead:


I know this is pretty weird..

mvp_badge
MVP
Rank: #128

i saw ur last snap what is wrong.its giving u correct what else u need?


if u want additional info then u have to apply aggregate function on other column which u need in outcome.


i hope that will help you

mvp_badge
MVP
Rank: #128

Ok let's do it step by step,


firstly remove all filters and then apply group by on date.

please share snap

Hi Jitendra, I removed all the filters and sorts and it still shows the same:

I don't think it's the code and technical, should I contact the Outsystems support?

mvp_badge
MVP
Rank: #128

ok good now please share snap of table view data

mvp_badge
MVP
Rank: #69

Let's simplify things a bit.

The DateTimeToDate() isn't needed because MyRequest.RequestDate and UpdateDate are both of type Date. The same for Activity_Inbox. At least in the oml sent.

In your place, I'd start with a simpler screen. Just to be sure you can return data. Then I'd apply the group by and a count and show that.

If that works, there is nothing wrong.

Hi Nuno, yes of course I don't convert it anymore because it already has the same type of data. In the previous one sure I need to convert because it different, but now I changed the attribute data type just to make sure. I think I'd understand the basic pretty well. To make it clear I'll show step by step on how I did it right now.

Here's the entity with no filters or any requirement in the aggregate:



Clearly it can shows all of the records well. Now I just added a "Group By" to the "RequestDate" attribute, still with no filter or whatsoever. But here's what I got instead:



It shows "No data" while in the entity itself contains many record in the previous picture. The funny thing is when I try to add a Group By to another attribute, this thing is worked:



The "UpdateDate" attribute has the same data type to "RequestDate" attribute. Is it because the records that previously exist contained Time value? But I think it's not because usually it still works.. I don't know yet.

mvp_badge
MVP
Rank: #69

It might be...

Data wasn't changed, it is simply shown differently.

And in that case, you do need to use DateTimeToDate.


Do you have access to the SQL to check it?

mvp_badge
MVP
Rank: #69

You copy the  Executed SQL from the Aggregate an try it on SQL.

If it doesn't work, remove the group by and see the values returned. They should be date.


Play with SQL a bit and you will see what is wrong.

Hi Nuno, this is what happened when I did it:



Maybe I don't actually have the access to the User entity?

mvp_badge
MVP
Rank: #69

That script is to run on the SQL Server, not on Service Studio.

Service Studio only works with entity names.