Use Queries to show records based on conditions

Follow

Comments

32 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

  • 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

  • Avatar
    Francesca G

    Hello! Is it possible to order with a random criteria? Thanks!

  • Avatar
    Awesome Table

    Hi Francesca G,

    The only way to display data randomly is to change the existing proxy with a new that mix the rows between them. Because the API we used put the data in cache and doesn't allow to change the order of your rows.

    Kind regards,
    Thomas

  • Avatar
    Randolph Abelardo (Edited )

    Hi @Francesca G.

    I agree with Thomas. There's currently no direct way to do this within the Awesome Table web app.

    With a little bit of Google Sheets magic & Awesome Table's Query, we can achieve your desired end-result.

    Disclaimer:
    This topic is beyond the scope of Awesome Table support. I can only offer a best effort aid since I only know the basics of Google Sheets. I suggest reaching out directly to the Google Docs Help Forum if you need further assistance regarding this topic.

    With that said, here's how I approached that same issue in another case:

    I used the pi() & rand() functions in my sample to generate random numbers: = rand() * pi() + pi().
    * See screenshot below for the outcome. Title # is the actual sequence in GSheets. Note the ordering when displayed in AwesomeTable (via Query statement).

    Hope this helps. 

     

    - Randy

     

  • Avatar
    Francesca G

    Hi @Randolph Abelardo, great function to generate a random number. I made something similar but it wasn't so meticulous! After adding this column (in my case AA), I added to the query ORDER BY AA. I see the rand number changes when editing or refreshing the spreadsheet, but doesn't seem to change on each refresh on the awesome view. Any hint to decrease cache or force the reload of this column? Thanks again!!

  • Avatar
    Randolph Abelardo (Edited )

    Hi @Francesca G.

    I just learned from Awesome Table Support Thomas that my recommended setup won't work because the API used puts the data in cache and doesn't allow to change the order of the records. Apologies for the misinformation.

    - Randy

Please sign in to leave a comment.