Data from 3 Tables to one

[Sergio_Sa]

Hi guys.
I have 3 tables (INFOPREX_XX) with the same structure, which concern 3 stores. Each table has multiple products in rows with monthly sales in columns. Using the product code as a key ( [CPR]), I needed to put multiple sales per month in the same row to make an overall analysis of the 3 stores. My approach was to use:

LOOKUP([_THISROW].[CPR], “INFOPREX_CS”, “CPR”, “V_1”)
to one month from other store and:

LOOKUP([_THISROW].[CPR], “INFOPREX_BL”, “CPR”, “V_1”)+
LOOKUP([_THISROW].[CPR], “INFOPREX_CS”, “CPR”, “V_1”)+
[V_1]
for the total.
But there are many columns (24 x 3) and the app is getting too slow to sync. Is there any better solution?
Thank you for taking the time.

[Marc_Dillon]

My first thought is, why not combine them all into one table, with an additional “Store” column?

[Steve]

I agree with @Marc_Dillon.

[Sergio_Sa]

I don’t know if I got the suggestion.
Create a table with data from the 3 tables? In Appsheet or in spreadsheet source ?

In Appsheet I had the idea that I read that was not possible and if it is I don’t now how.
In spreadsheet would have to manually join the 3 files, which was not the intended solution. Each file has 17k rows and not in the same order.

But still how could I find the totals per month of the 3 stores in each row?
Can you point me to that please.

[Marc_Dillon]

The suggestion was to merge the 3 tables into 1 in the spreadsheet, as well as add another column to identify the store per record. This would allow a SELECT() expression to be used, with a condition to identify the product, thus you can get rid of the LOOKUP functions, as well as provide easier scaling if you happen to add additional stores in the future.

Your solution does work, correct? But it is just slow?

Are you using this expression in a virtual column? Having to constantly calculate this value upon every change for 17k x 3 records is definitely going to create a slow-down.

Maybe you can create a new table for “Reporting” where you select a number of products that you want totals for. That way the app will only have to calculate this value a much smaller number of times, and only on-demand, instead of constantly.

[Sergio_Sa]

Thank you very much @Marc_Dillon , i will follow your suggestion and try and fail and try …