I have appliation that loads large amount of data and have many charts and tables that shows analysis. However I have requirement to load a filtered list from of accounts from those that are already loaded and do a analysis on only filtered records.
I have attached 2 files. Source List that will be initially loaded. Then filter data based on filtered_list which will be used for analysis on one tab.
Load the filtered list first and then load the matching records from the source list using the exists() function:
Accounts:
LOAD AccountID
FROM
filtered_list.xlsx
(ooxml, embedded labels, table is Sheet1);
Amounts:
LOAD AccountID,
[Company Name],
Date,
Amount
FROM
source_list.xlsx
(ooxml, embedded labels, table is Sheet1)
where exists(AccountID);
Or you can use left or right join. For example:
Accounts:
LOAD AccountID,
[Company Name],
Date,
Amount
FROM
source_list.xlsx
(ooxml, embedded labels, table is Sheet1)
right join (Accounts)
LOAD AccountID
FROM
filtered_list.xlsx
(ooxml, embedded labels, table is Sheet1);
Or simply load everything and create a bookmark for the selection of accounts from your filtered list. You can then simply activate that bookmark when you go to the analysis tab. You could also automate that with the OnActivate sheet trigger with an Activate Bookmark action that activates the filtered accounts bookmark.
However reload is not an option. so application will be loaded on the server. user will download the reload app and then apply his/her own filtered list and do the analysis based on that.
in the above case user does not have link to source data for reload. all he can do is filter the records based on his list.
As I said. Load everything and let the user create a bookmark for his selection. An alternative is to give the user the option to paste a list of account numbers in a search box. See attached example.
Application is loaded on the server side. The end user cannot reload the app. However he can import accounts numbers and wants to run the analysis only on those accounts.
