Queries

Follow

Comments

25 comments

  • Avatar
    Davide Garino

    Is it possible to perform multiple 'order by' queries?

    For example I would like to order a table by a column (e.g. price) and then by another column (e.g. publish date) in order to have the table ordered by the publish date and for each publish date ordered by price.

    Thank you!

  • Avatar
    Awesome Table

    Hi Davide,

    Indeed it is possible, you just need to apply "order by F, H desc". I suggest you to consult this website to have additional informations: https://developers.google.com/chart/interactive/docs/querylanguage#order-by.

    Thank you.

    Thomas

  • Avatar
    Paul Cardelli (Edited )

    I would like to add a query to filter by current weekday. Where a colum row might have multiple weekday name such as Monday, Wednesday, Friday

    If the current Weekday is Friday only the rows with Friday would be shown.

    I know the following is wrong but any help would be appreciated.
    Select * where J contains text(dayofweek(now()),"dddd")

  • Avatar
    Awesome Table

    Hi Paul,

    The solution is:
    select * where dayOfWeek(D) contains dayOfWeek(todate(now())) order by D desc

    Note that days are 1-based, so the function returns 1 for Sunday, 2 for Monday, etc.

    Regards,
    Thomas

  • Avatar
    Paul

    How to add pound sign in where clause? I have the following query but it is not filtering properly

    SELECT * where K contains '#'

    Thanks!

  • Avatar
    Awesome Table

    Hello Paul,

    Would it be possible for you to share your Awesome Table view and your spreadsheet at "[email protected]"? Make sure you give us access to your spreadsheet. If you have sensitive data, you can make a copy with similar data.

    Kind regards,
    Thomas

  • Avatar
    Jameson Elder

    Is there a way to filter out data, for instance, I have a column that either has a Name or "none" in the cell. Can I remove all the cells that contain "none"

  • Avatar
    Awesome Table

    Hello Jameson Elder,

    Indeed it is possible to remove one of your value in your column if you want. 
    You just need to use " select * where not A = '' ".

    For example if you want to remove all your values "none" in your column A, you need to apply:
    select * where not A = 'none'

    Kind regards,
    Thomas

  • Avatar
    Paul

    Hi Thomas,

    I have shared the spreadsheet to your support email. here is the link for the awesome table. https://awesome-table.com/-L6-f3cJLn1i1N10_pB5/edit

    I'm not sure though if there is an easier way to grant edit access besides sharing the link and ask to request for edit access.

    Thanks.

  • Avatar
    Hannah (Edited )

    Can we alias column names using queries? Eg, I have a table that references a Google Form, so the column names are long-ish questions from the Form. But I want to create an overview table that has more concise column names.

  • Avatar
    Awesome Table

    Hi Paul,

    Indeed we discovered that the hash doesn't work with this query. We will fix that as soon as possible and we will keep you informed when it will be solved.

    Thank you very much for reporting this issue!

    Thomas

  • Avatar
    Awesome Table

    Hi Hannah,

    Indeed it is possible to rename columns headers in your Awesome Table view. For example if you want to change your title of your column C to "Name", you use this query: select * label C 'Name'.
    You can find all informations you need in this article.

    Thank you.

    Thomas

  • Avatar
    Randolph Abelardo (Edited )

    Good question, @Hannah.

    We've now incorporated the label topic in the Query article.

    The hands-on exercise is tagged under Task # 6 above.

    - Randy

  • Avatar
    Execs1960

    I can't seem to get a multiple "WHERE" statement to work.  Example:

    This query works:

    SELECT A,B WHERE A <> "Retired" ORDER BY A ASC

     

    These queries do not work:


    SELECT A,B WHERE A <> "Retired" AND WHERE A <> "Honorary" ORDER BY A ASC

    SELECT A,B WHERE A <> "Retired" AND <> "Honorary" ORDER BY A ASC

    I consulted the Google query language website and is indicates that multiple WHERE queries should work with AND or OR.  Not working for me.  

    What am I screwing up?

  • Avatar
    Awesome Table

    Hi Execs 1960,

    I think it should work if you use this formula:
    SELECT A,B WHERE A <> "Retired" AND A <> "Honorary" ORDER BY A ASC

    Kind regards,
    Thomas

     

  • Avatar
    Randolph Abelardo (Edited )

    @Execs1960,

    The case you shared is covered / discussed under task #s: 10, 12, 14, 15, & 19. These shows examples that use multiple conditions or criteria using either the AND or OR comparison operators.

    You can also simply omit ASC since sorting the data in ascending order is the default behavior. This info can also be found in this tutorial.

  • Avatar
    Paul

    Hi Thomas,

    Any update on the issue of hash in queries? Thanks.

  • Avatar
    Awesome Table

    Hi Paul,

    Indeed this issue is now solved and it works well since yesterday.

    Kind regards,
    Thomas

  • Avatar
    Sfaulkner

    Hey,

     

    I noticed that it says that queries won't work with proxy setttings.  Is there anyway around this?

     

    Regards,

     

    Simon.

  • Avatar
    Awesome Table

    Hi Sfaulkner,

    You can try to use the "query()" formula and get the results of this formula. But you need to use header = 0 (the third parameter of the formule) otherwise it will concatenate the two first lines and it won't work in Awesome Table.

    Kind regards,
    Thomas

  • Avatar
    Gary Marks

    Hello,

           I am trying to determine the best way using either a query or conditional display to accomplish the following.

    I would like a column to only display content when a specific category is selected from another column using the categoryfilter. Example; My form contains a categoryfilter column for State/Country with one option being "International". In another column, I have the name of the country. I would like this column to only display when "International" is selected using the filter option.

    Is this possible? What would be the best way to accomplish this?

    Thank you,

    Gary

    FYI, new to awesome table, and loving it.

  • Avatar
    Awesome Table

    Hello Gary,

    If I understand you correctly, you want to hide a column by default and show it according the selected value in you filter of your first column?
    If that is the case, unfortunately, it is not possible. You can't display or not a column according to a selected filter.

    The only solution, I can suggest you is use Dependent Categories Filters. It allows you to link multiple categories filter.

    Kind regards,
    Thomas

  • Avatar
    Laura Ruiz (Edited )

    Hi there,

    I was wondering if it's possible to query the columns using their headers. Something like

    select Name, Age 

    Instead of 

    select A, B

     Thanks a lot!

  • Avatar
    Awesome Table

    Hi Laura Ruiz,

    Unfortunately, it is not possible to use the headers of columns in your query! The queries work exclusively with the column IDs (identifiers).

    Kind regards,
    Thomas

  • Avatar
    Randolph Abelardo (Edited )

    Hi @Laura

    I agree with @Thomas. This required syntax is based on the prescribed method by the Google Visualization Query Language:

    Column IDs in spreadsheets are always letters. You must use the ID, not the label, in your query string.

    This article merely serves as an overview of what Queries can do - with focus on real-world and practical examples. To learn more about the particulars, please read the Google Visualization Query Language.

    Hope this clarifies everything.

    - Randy