Use Queries to show records based on conditions

Follow

Comments

29 comments

  • Avatar
    Gustavo A. Valero P.

    Hi,

    I have a little problem using LABEL and ORDER clauses at the same time.

    If I write:

    SELECT A, C, D ORDER BY D       -->   the query works fine

    but with:

    SELECT A, C, D LABEL A ' Name', C 'Phone' ORDER BY D   -->   the query sends an error message!!

    Is there a little bug or I'm missing anything? 

    Thanks in advance for your help!

    Gavp

    BTW...  great web site, product and ideas, congratulations!!

    0
    Comment actions Permalink
  • Avatar
    Awesome Table

    Hello Gustavoavalerop,

    Actually, when you use SELECT and ORDER, ORDER must be just after SELECT. I suggest you use:

    SELECT A,C,D ORDER BY D LABEL A 'Name', C 'Phone'

    It should work with this method.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Gustavo A. Valero P.

    Hi Thomas,

    Yes, you are right, now my query works correctly!

    Thanks

    Gavp

    0
    Comment actions Permalink
  • Avatar
    Lasse Soininen

    Is it possible to pass query via URL parameter? We would like to limit returned results to a UID that would be passed in the URL view?UID=123456789

    Query: select * where UID = {{GET_UID}}

    0
    Comment actions Permalink
  • Avatar
    Awesome Table

    Hello,

    Unfortunately this is not possible with Awesome Table.

    Nicolas

    0
    Comment actions Permalink
  • Avatar
    Sean Gillard

    Great Article! it helped me on some of the things I have been working on.

    I have one that I'm asking it to essentially filter more. But once I get to a certain point, Then the rest stops working. What am I doing wrong?  In the below formula, A is bringing me Active and inactive. Manager sometimes shows up and sometimes doesn't depending on if I add the Active one or not. Is there a specific way this order should be to make it work?

    =QUERY(Lookup!A:Y, "select B, E, F, O, P, Q, W, X where B = 'Operations' and W = 'Supervisor' or W= 'Lead' or W = 'Manager' and A = 'Active' ")

     

     

    Sean

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Sean Gillard,

    Actually, your syntax is not correct. You don't need to use"=QUERY(Lookup!A:Y...".

    Your query must be like this:

    select B, E, F, O, P, Q, W, X where B = 'Operations' and W = 'Supervisor' or W= 'Lead' or W = 'Manager' and A = 'Active' 

    In this way, it will work because that's the correct syntax.

    Thank you.

    Thomas

     

    0
    Comment actions Permalink
  • Avatar
    Jennifer Prusak

    I am super new at this. Is there a way to make it so as you filter results in the drop-down filters that the other filters would be dynamic and update with only content relative to the filter before it? If so where would you beginning to set that up?

     

    Thank you so much for your help!  Jen

    1
    Comment actions Permalink
  • Avatar
    Nicolas Gauvin

    Hello Jennifer,

    You could achieve this with CSV and Category filters using dependent filters.

    Please let me know if that helps!

    Nicolas

    0
    Comment actions Permalink
  • Avatar
    Dave HUDDART

    Hello

    Is it possible to SELECT rows that are not duplicates (based on value in a specified column)?
    I can deduplicate in Google Sheets, but was wondering if I could do it in an Awesome Table view.

    Best regards

    Dave

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Dave HUDDART,

    It is definitely possible to select rows depending on specific term or criteria. You should consult this part of this article to know how to achieve that.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Brandon Carraway

    🙌🙌

    The SORT option was VERY helpful; I needed newest data at the top, and doing this through Google Sheets is tedious. This saved me SO much headache!!! ❤❤

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Brandon Carraway,

    Thank you for your message! 

    We are really glad you like our product and you find it is useful.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Luís Esteves

    Hi,

    Is it possible to add a button to sort the results in diferent ways ? I'd like to have the possibility to sort by one colums (alphebeticaly) or a diferent way by clicking on a button.

     

    Thanks !

    0
    Comment actions Permalink
  • Avatar
    Nicolas Gauvin

    Hello Luis Esteves,

    There are no ways to change the sorting or query with a button.

    Nicolas

    0
    Comment actions Permalink
  • Avatar
    Kris S

    Hi,

    I need help on this one

    =QUERY('Raw Data New'!A:BS,"select A,BN,BO,BQ,BR,BP,BM,H,AA,AB,AH,AI,AJ,AK,AL,AM,AN,X,Y,AQ,AR,AS,AT,AU,AV,AW where BP = date '"&TEXT($AN$7,"yyy-mm-dd")&"'")

    AN7 is the cell reference date and I need pull all dates that includes cell reference plus 7 days or 1 week prior to the cell reference date.

    Thank you!

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello Kris S,

    We don't deliver specific support for queries, so, I suggest you consult this article that will give you the language to achieve what you want.

    Kind regards.

    Thomas

    0
    Comment actions Permalink
  • Avatar
    Brandon Carraway

    How to sort two columns (L, I)

     

    I need to sort by column L desc, then by I desc I tried this: "select * order by L desc and order by I desc" even using a "," instead of "and" as I was reading through the above guide, but I can't seem to figure it out. 🤦‍♂️

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo (Edited )

    Hi @Brandon.

    Instead of select * order by L desc and order by I desc, use this statement:

    select * order by L desc, I desc

    Please reference Task # 4 above for more info.

    Hope this helps. 

    - Randy

    0
    Comment actions Permalink
  • Avatar
    Florentin von Haugwitz

    Hello,

    is it possible to use a group by statement?

     

    Thank you

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo (Edited )

    Hi @Florentin.

    The group by clause can and will work when entered in the Awesome Table editor's Query field.
    But I don't recommend using this.
    Please note that since the group by clause can only be used with an aggregation function, it will perform the specified aggregation (avg, count, max, min, sum) and in effect:

    • Bypass your original datasource (negating the parameters [2nd] row containing filters and keywords)
    • And will use the aggregated output you specified, instead (only the columns you specified in the Query field will be displayed
    • This will also render your template useless. 

    An alternative would be to use the order by clause to cluster items based on a specified category. More about this on Tasks # 3 - 4 above.

    Hope this helps.


    - Randy

    0
    Comment actions Permalink
  • Avatar
    zoran perovic (Edited )

    Hi there,

    i have problem

    In Table have 4 columns

    A                         B                          C                        D

     ID              01-jan-2019          02jan-2019      03-jan-2019

    1                      Yes                       No                       Yes

    2                      Yes                      Yes                       Yes

    3                      No                        Yes                       No

    4                      Yes                      No                       Yes

    5                       No                       No                       No

    How to get result which shows the same value(s) (yes) on each date? (the same order)

    If i search for 2 results per column then the result will be: columns B (rows 1,2), C(rows 2,3 ), D( rows 1,2)

    If  i search for 3 results per columnd then result will be: column B (rows 1,2,4) and column D (rows 1,2,4)

    0
    Comment actions Permalink
  • Avatar
    Thomas Morin

    Hello @zoran perovic,

    You could use a filter for several columns and limit the number of items displayed. I suggest you use a category filter for your B, C, and D column. It is definitely possible to achieve that with Awesome Table.

    That's why you should consult this article that explains how to set up a stringFilter for several columns but it is the same principle except that you need to use categoryFilter instead of stringFilter. Here are all our available filters.

    Kind regards.

    Thomas

    1
    Comment actions Permalink
  • Avatar
    zoran perovic

    well thank you Thomas

    but i am using excel for work on this

    is there any tip how to get this result in excel?

     

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo

    Hi @Zoran.

    There seems to be some confusion.
    MS Excel is beyond the scope of Awesome Table support (we don't use Excel in our web app). 
    Please reach out to the proper support channel for your concerns.
    Thanks.

    - Randy

    0
    Comment actions Permalink
  • Avatar
    fabricio suarez

    Hello, is there a template about movies, my question is how can I make a movie have several genres?
    Example:
    Avengers: Action / adventure / science fiction / fantasy / superheroes
    When searching for any of the genres, it appears.
    Thank you

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo (Edited )

    Hi @fabricio.

    Please scan our Template Gallery to see a sample of a movie template.
    And csvFilter is the best option for your needs.

    Note: For topics unrelated to any article, please create a new post tagged under General Discussion so we can address all questions accordingly.

    - Randy

    0
    Comment actions Permalink
  • Avatar
    Marco Fiorentino

    Hi,

    it's possible to show columns based on filters?

    I'd like to have it working like this:
    If in the filter for column A I select the first value I see only columns B, C and D
    If in the filter for column A I select the second value I see only columns E, F and G

    Thanks,
    Marco

    0
    Comment actions Permalink
  • Avatar
    Randolph Abelardo

    Hi @Marco.

    We can only use Queries to show records (rows), but not columns, based on conditions.

    - Randy

    0
    Comment actions Permalink

Please sign in to leave a comment.