Queries

Follow

Comments

20 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 "suppor@awesome-table.com"? 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