Query on local storage with "case when"

Query on local storage with "case when"

  

Hello,

Here I´m again with other doubt:

How can I perform more specific/complex queries using local storage, like the one below?


Thank you!!

Hi Thiago,

When we talk about mobile applications, we don't have options of advance sql query. If you want to use complex query, you can write server action to fetch value from database then you can store in local storage.


Thanks,

Rajat Agrawal

Rajat Agrawal wrote:

Hi Thiago,

When we talk about mobile applications, we don't have options of advance sql query. If you want to use complex query, you can write server action to fetch value from database then you can store in local storage.


Thanks,

Rajat Agrawal

Thanks,

Rajat

Thiago Caixeta wrote:

Hello,

Here I´m again with other doubt:

How can I perform more specific/complex queries using local storage, like the one below?


Thank you!!

Hi Thiago,

I understand your concern on this. 

You can not use advanced queries as well as server actions as you are working with your local storage.
With the help of server actions, you can not achieve local storage data, as its not on server but on your device.

So only you can go with Local Storage Aggregates.
In local storage aggregates, you can define inner joins and conditional columns as required in your SQL logic.

Also you manipulate data as per your requirement inside your custom client side logic, but I don't recommend that as it will have several loops.

Regards,
Palak Patel


Rajat Agrawal wrote:

Rajat Agrawal wrote:

Hi Thiago,

When we talk about mobile applications, we don't have options of advance sql query. If you want to use complex query, you can write server action to fetch value from database then you can store in local storage.


Thanks,

Rajat Agrawal

Thanks,

Rajat

Yes....I was doing some additional research about this topic....I think maybe it will be the only option...

But my idea was not to depend on internet, once the app is using just local storage...As the app uses local storage, the only part of it using online requests could be this one....

Thank you for your help Rajat


Palak Patel wrote:

Thiago Caixeta wrote:

Hello,

Here I´m again with other doubt:

How can I perform more specific/complex queries using local storage, like the one below?


Thank you!!

Hi Thiago,

I understand your concern on this. 

You can not use advanced queries as well as server actions as you are working with your local storage.
With the help of server actions, you can not achieve local storage data, as its not on server but on your device.

So only you can go with Local Storage Aggregates.
In local storage aggregates, you can define inner joins and conditional columns as required in your SQL logic.

Also you manipulate data as per your requirement inside your custom client side logic, but I don't recommend that as it will have several loops.

Regards,
Palak Patel


Palak,

I tried to build this "query" using aggregates, but I could not achieve my goal with that. I thought about the idea you mentioned, to manipulate the client side logic, but the loops concerned me, so I´m avoiding this option for now...

Thanks for your reply!


Hi Thiago,

When we talk about complex logic sometime we are not able to achieved by using aggregates. So, my suggestion is that when user login first time at that time you need to validate users via internet, right?

At that time only call server action and pull all data in local storage then if the app is offline or no internet then also you can use local storage data.


Thanks,

Rajat Agrawal

Rajat Agrawal wrote:

Hi Thiago,

When we talk about complex logic sometime we are not able to achieved by using aggregates. So, my suggestion is that when user login first time at that time you need to validate users via internet, right?

At that time only call server action and pull all data in local storage then if the app is offline or no internet then also you can use local storage data.


Thanks,

Rajat Agrawal

I think this will be the best approach, I have already started to modify the app to do that.

In time, I´m searching for the option to add a customized sql query (to run as server action) but ím not finding it...

Where can I find it?


Thiago Caixeta wrote:

Rajat Agrawal wrote:

Hi Thiago,

When we talk about complex logic sometime we are not able to achieved by using aggregates. So, my suggestion is that when user login first time at that time you need to validate users via internet, right?

At that time only call server action and pull all data in local storage then if the app is offline or no internet then also you can use local storage data.


Thanks,

Rajat Agrawal

I think this will be the best approach, I have already started to modify the app to do that.

In time, I´m searching for the option to add a customized sql query (to run as server action) but ím not finding it...

Where can I find it?


Found....It is necessary to fetch data from other data sources and create a data action!

Hi Thiago,


All is good now or anything more you needed from my side.


Thanks,

Rajat Agrawal

Solution

Palak Patel wrote:

So only you can go with Local Storage Aggregates.
In local storage aggregates, you can define inner joins and conditional columns as required in your SQL logic.

Hi Thiago,

Expanding on what Palak Patel explained, you can add attributes to your Aggregates, where you can use the If(condition, value-if-true, value-if-false) function as formula, in a similar fashion to how you would do it in SQL SELECT with CASE WHEN condition THEN value-if-true ELSE value-if-false END.

In your case, I believe this might work if you:

  • Group By attributes {Item2}.[Description] and {Item3}.[Date]
  • Sum attribute {Item3}.[Total] (call it something like... Total)
  • From the context menu of attributes {Item2}.[Description], {Item3}.[Date] or Total, create a New Attribute for each month, with condition: If(Month({Item3}.[Date]) = 1, Sum, 0) for attribute Jan, and so on.

In the end you will have 15 blue-colored attributes in your Aggregate({Item2}.[Description], {Item3}.[Date], Total, and 12 months) and you can ignore the value of  the Total attribute in your screen afterwards. 

Hope this helps.

Solution

Rajat Agrawal wrote:

Hi Thiago,


All is good now or anything more you needed from my side.


Thanks,

Rajat Agrawal

Thank you very much, it is clear.


Jorge Martins wrote:

Palak Patel wrote:

So only you can go with Local Storage Aggregates.
In local storage aggregates, you can define inner joins and conditional columns as required in your SQL logic.

Hi Thiago,

Expanding on what Palak Patel explained, you can add attributes to your Aggregates, where you can use the If(condition, value-if-true, value-if-false) function as formula, in a similar fashion to how you would do it in SQL SELECT with CASE WHEN condition THEN value-if-true ELSE value-if-false END.

In your case, I believe this might work if you:

  • Group By attributes {Item2}.[Description] and {Item3}.[Date]
  • Sum attribute {Item3}.[Total] (call it something like... Total)
  • From the context menu of attributes {Item2}.[Description], {Item3}.[Date] or Total, create a New Attribute for each month, with condition: If(Month({Item3}.[Date]) = 1, Sum, 0) for attribute Jan, and so on.

In the end you will have 15 blue-colored attributes in your Aggregate({Item2}.[Description], {Item3}.[Date], Total, and 12 months) and you can ignore the value of  the Total attribute in your screen afterwards. 

Hope this helps.

I understood Jorge. We have two possible solutions: Using Database queries and Local Storage. 

Maybe using query from Database is the best approach, anyway, I think I will test your example, so I can avoid internet this time...

It is good cause every soluction I test is a learning for me!

Thank you for your suggestion.


Jorge Martins wrote:

Palak Patel wrote:

So only you can go with Local Storage Aggregates.
In local storage aggregates, you can define inner joins and conditional columns as required in your SQL logic.

Hi Thiago,

Expanding on what Palak Patel explained, you can add attributes to your Aggregates, where you can use the If(condition, value-if-true, value-if-false) function as formula, in a similar fashion to how you would do it in SQL SELECT with CASE WHEN condition THEN value-if-true ELSE value-if-false END.

In your case, I believe this might work if you:

  • Group By attributes {Item2}.[Description] and {Item3}.[Date]
  • Sum attribute {Item3}.[Total] (call it something like... Total)
  • From the context menu of attributes {Item2}.[Description], {Item3}.[Date] or Total, create a New Attribute for each month, with condition: If(Month({Item3}.[Date]) = 1, Sum, 0) for attribute Jan, and so on.

In the end you will have 15 blue-colored attributes in your Aggregate({Item2}.[Description], {Item3}.[Date], Total, and 12 months) and you can ignore the value of  the Total attribute in your screen afterwards. 

Hope this helps.

What is the "sum" fucntion in If(Month({Item3}.[Date]) = 1, Sum, 0) ?


Sorry, my bad... the value inside the If's False parameter should be Total (and not Sum). it's the new attribute calculated on the previous step.

Thiago Caixeta wrote:

I understood Jorge. We have two possible solutions: Using Database queries and Local Storage. 

Maybe using query from Database is the best approach, anyway, I think I will test your example, so I can avoid internet this time...

It is good cause every soluction I test is a learning for me!

Thank you for your suggestion.


Great to see you found the direction to solve your query.

Keep coding!


Rajat Agrawal wrote:

Hi Thiago,

When we talk about complex logic sometime we are not able to achieved by using aggregates. So, my suggestion is that when user login first time at that time you need to validate users via internet, right?

At that time only call server action and pull all data in local storage then if the app is offline or no internet then also you can use local storage data.


Thanks,

Rajat Agrawal

This is indeed a good solution.
So that you are getting the desired data easily from server.
But try to limit the data only as per your requirements. Else it will impact the mobile app performance if you are syncing a heavy data from server to local on login or app start.

Regards,
Palak Patel


Palak Patel wrote:

Rajat Agrawal wrote:

Hi Thiago,

When we talk about complex logic sometime we are not able to achieved by using aggregates. So, my suggestion is that when user login first time at that time you need to validate users via internet, right?

At that time only call server action and pull all data in local storage then if the app is offline or no internet then also you can use local storage data.


Thanks,

Rajat Agrawal

This is indeed a good solution.
So that you are getting the desired data easily from server.
But try to limit the data only as per your requirements. Else it will impact the mobile app performance if you are syncing a heavy data from server to local on login or app start.

Regards,
Palak Patel



Hi Palak,

Yes, I agreed with you we need to limit the data otherwise it impact the performance.


@Palak - We already experience this in the past.. :)


Thanks,

Rajat

Thiago Caixeta wrote:

Rajat Agrawal wrote:

Hi Thiago,


All is good now or anything more you needed from my side.


Thanks,

Rajat Agrawal

Thank you very much, it is clear.


Welcome

Rajat Agrawal wrote:

Hi Palak,

Yes, I agreed with you we need to limit the data otherwise it impact the performance.


@Palak - We already experience this in the past.. :)


Thanks,

Rajat

Hi,
Yes @Rajat, I remember, that's why I have highlighted that point here. :D:D

Jorge Martins wrote:

Sorry, my bad... the value inside the If's False parameter should be Total (and not Sum). it's the new attribute calculated on the previous step.

Just a clarification: This "Total", is the  {Item3}.[Total] or Sum({Item3}.[Total]) new attribute?

From the Aggregate you show, there are two clarifications to be made:

  1. Instead of using the "+ New Attributes" button to the right of your Aggregate, like you've been doing, you should hover the mouse over one of the blue-header columns, click on the little square with a down-arrow that appears on the corner of the column and choose the "+ New Attributes" entry from that menu.
  2. The Expression for Jan (after you follow the previous step) would be
    If(Month(Lancamentos.Data) = 1, Total, 0), where Total is the new column resulting of applying Sum to Lancamentos.Valor.

Jorge Martins wrote:

From the Aggregate you show, there are two clarifications to be made:

  1. Instead of using the "+ New Attributes" button to the right of your Aggregate, like you've been doing, you should hover the mouse over one of the blue-header columns, click on the little square with a down-arrow that appears on the corner of the column and choose the "+ New Attributes" entry from that menu.
  2. The Expression for Jan (after you follow the previous step) would be
    If(Month(Lancamentos.Data) = 1, Total, 0), where Total is the new column resulting of applying Sum to Lancamentos.Valor.

I understood....I did these changes....but it is still not grouping, see:


Is there any other configuration to be made?


Tks

Hi again Thiago,

You are using Group By on your Date attribute... that means you will have different entries for each different day...

I'd say you need to:

  1. Create a + New Attribute (this time you can use the right-most one, directly available on the aggregate)
    1. Set the attribute's Name to Month
    2. Set the attribute's Formula to Month(Date).
  2. Instead of grouping by your Date attribute, group by the new Month attribute.
  3. On your months attributes (Jan, Feb, etc...), use Month = 1 (and Month = 2, etc...) as the formula.

Jorge Martins wrote:

Hi again Thiago,

You are using Group By on your Date attribute... that means you will have different entries for each different day...

I'd say you need to:

  1. Create a + New Attribute (this time you can use the right-most one, directly available on the aggregate)
    1. Set the attribute's Name to Month
    2. Set the attribute's Formula to Month(Date).
  2. Instead of grouping by your Date attribute, group by the new Month attribute.
  3. On your months attributes (Jan, Feb, etc...), use Month = 1 (and Month = 2, etc...) as the formula.


Hello,


Thank you very much!!!!

Finally, it is done.

Thank you all for the help!!

Anytime!

Please make sure the post that best answers your initial question is marked as the answer (or add your own solution post if you followed another approach).