Aggregate Group By with Attribute failed to show the records

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.

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.

Hi,


Please below snape for reference,


JitendraYadav wrote:

Hi,


Please below snape for reference,


That should work. Something people miss out very frequently and then keep wondering where we went wrong.


Cheers. 



Hi everyone, thanks for the responses.


JitendraYadav wrote:

Hi,


Please below snape for reference,



I already tried the method above (create a dummy attribute, convert the data type to date only) but it still did not work. Is it because the records still contained the Time value so I have to re-create the whole entity?

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

You can use advanced SQL to resolve this issue.

Aditya Eka Prabowo wrote:

Hi everyone, thanks for the responses.


JitendraYadav wrote:

Hi,


Please below snape for reference,



I already tried the method above (create a dummy attribute, convert the data type to date only) but it still did not work. Is it because the records still contained the Time value so I have to re-create the whole entity?

after that you need to group by this cloumn.

Regards

Rahul Sahu


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?

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

Aditya Eka Prabowo wrote:

Hi Rahul,


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

Thanks before

Hi Aditya, you need to create an ad-hoc attribute as you see in the pic with:

value = DateTimeToDate(MyRequest.RequestDate)

After group by this new attribute

Check the oml attached, it is in the myRequest aggregate

Hope I could help you

Best regards

Carlos Lessa

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..

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

JitendraYadav wrote:

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


No I think it should have show the records instead of "No data", just like this example:


But unfortunately it did not..

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?

ok good now please share snap of table view data

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.

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?

Nuno Reis wrote:

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?


Yes, how to do so? Do you have any advise?

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?

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

Service Studio only works with entity names.