Google Sheet Query Language

[Ale]

I'm having issues in performing a query that allows me to retrieve the percentage of each expense, filtered by category, using Google sheet Query Language (https://developers.google.com/chart/interactive/docs/querylanguage).

Does anyone help me?

I need an output like this:

Filtered by Category=Fun
Reason  Percentage
Biliardo 2,17%
Birra 23,91%
Cena 28,87%
Cinema 12,17%
Metro 6,43%
Paninaro 7,39%
Pizza 6,96%
Spesa 12,82%

The input is the following table (in CSV):
Import;Date;Reason;Source;Category
-4,40;01/02/2024;Metro;Credit Card;Fun
-10,00;01/02/2024;Birra;Credit Card;Fun
-2,50;02/02/2024;Biliardo;Credit Card;Fun
-8,00;03/02/2024;Pizza;Credit Card;Fun
-63,14;03/02/2024;Benzina;Credit Card;Outcome
-14,74;10/02/2024;Spesa;Credit Card;Fun
-35,99;10/02/2024;Regali;Credit Card;Additional outcome
5,50;12/02/2024;Ricarica ticket;Ticket restaurant;Income
-14,00;12/02/2024;Cinema;Credit Card;Fun
-5,50;17/02/2024;Birra;Credit Card;Fun
-5,50;17/02/2024;Birra;Credit Card;Fun
-8,50;17/02/2024;Paninaro;Credit Card;Fun
-11,64;18/02/2024;A Satispay;Credit Card;Additional outcome
11,64;18/02/2024;Da Credit Card;Satispay;Additional income
-127,00;19/02/2024;Tagliando;Credit Card;Additional outcome
-12,80;20/02/2024;Regali;Satispay;Additional outcome
-87,44;23/02/2024;Benzina;Credit Card;Outcome
-3,00;24/02/2024;Metro;Credit Card;Fun
-33,20;24/02/2024;Cena;Credit Card;Fun
-6,50;24/02/2024;Birra;Credit Card;Fun
-3,00;26/02/2024;Treno;Credit Card;Outcome
-996,99;26/02/2024;Assicurazione auto;Credit Card;Outcome
-3,00;26/02/2024;Treno;Credit Card;Outcome
1.941,11;27/02/2024;Stipendio;Credit Card;Income
-529,65;28/02/2024;Rata macchina;Credit Card;Outcome

[Ray Thomas]

I couldn't get your CSV file to work at all wthout making some changes to it, changing the commas to decimal points and the semicolons to commas. Even then, I couldn't get a query to work with it. I know the query works, because I used it in a Google Sheet I made in cell G1.

Until someone who knows how to do this answers you, I thought of two solutions that will work. You can either import the entire CSV file into your page as a DataTable then create a DataView and create a table from that. The other method is to import your CSV file into a Google Sheet and run the query against that to extract the data you need.

I illustrated both of those at https://brisray.com/google-charts/csv.htm 

[Ale]

Excuse me Ray, did you have a similar goal? Could you please share your use case? Maybe it could help me finding another idea/path.

I sent a CSV file to facilitate the forum to have an input table to work with. In my case I have a table in a sheet, daily filled in with new rows, each time I spend somthing (this example is part of my use case: check my expenses and income periodically).

Unfortunately the first column is made of decimal values in which the decimal part is separated by the integer one with a comma, just because in Italy the decimal representation is made with commas. I have to deal with this built-in characteristic in this country.

I looked at the link anyway, it's really interesting! Thank you for using my use case to help the community, and me too!

[Ray Thomas]

Hi Ale,

I have no use-case for using CSVs at all. What happened was that years ago I was working for a university looking after their website. They wanted graphing capabilities for their sites but between them, Web Services and IT wouldn't provide the means of connecting to any of our databases. I had a look around, chose Google Visualizations as the tool and uploaded the data I needed to Google Sheets and provided the tables and charts that way.

Years before that I was a database programmer, but that was way back in the days of things like FoxPro for DOS, but I still know quite a bit about SQL-like queries.

I just answer questions here where I think I can be of a little bit of a help. I like to show what I write actually works, at least for myself, and it doesn't take much to put together a page on my own site to show it.

Ray