I am trying to calculate the sum of groups of specific fields in a column. Also, I need to add capture that group name in a dropdown as well.
I have this load script running
Load categories, date , status, sales from salesdata
where Categories = 'Paper' or Categories = 'Glass'
Firstly, I want to capture sum of all highlighted fields 'Shoes', 'Pants', 'Socks', 'Shirts', 'Gloves', 'Sweatshirts' to be grouped together to name as clothes.
Second, I also have a Categories dropdown on the dashboard and I want "CLOTHES" to be reflected in the dropdown to filter the data. Please see attached files for the sample data
How can I achieve this? Any help is greatly appreciated!
Seems like a simple enough nested if() or match(), e.g.
if(Match(Categories,'Shoes','Pants','Socks','Shirts','Gloves','Sweatshirts'),'Clothes',Categories) as Categories_Grouped
Of course, this won't return any values in your load script since all of those are excluded by the Where condition requiring Categories = Paper or Glass.
You can't use this category in the WHERE clause - you'll have to spell them out individually. You could use a preceding load if you want to refer to the calculation, though, e.g.
Load * WHERE B = 2; Load A, A*2 as B INLINE [ A 1, 2];
Would you please elaborate more on how I can do the preceding load? I have data loaded from the database and where can I add this script.
Lib connect to '..........';
Load
Sales_Categories as Categories, date as Date, status as Status, sales as Sales;
[salesdata]:
Select
Sales_Categories , date , status , sales
from salesdata where Sales_Categories= 'Paper' or Sales_Categories= 'Glass' ;
** Adding this derived field in the where clause will solve my problem.
I'm attaching a spreadsheet with more clarity to explain what changes I want in a table with this derived field. This is just an example with mock dataset.
