How can we help?

New post
0

TABLE component : Display specific column based on first row values

Avatar
Eva DESSERTINE
None
Follow

Hi,

I'm struggling with a command that should be pretty easy to implement.

My datasource is a spreadsheet with issues labels in column A (Legal-Eco-Safety...), country names as the first row of each column (that would be my "column name") and status of different issues in rows.

I also have a user input control setting my variable for the country "ID_Country" for my entire dashboard.

What I need to do is display in my table component the information for the country that has been selected. Which means if Canada is selected and correspond to my column F, then the table selects and displays information from column F only.

It would be equivalent to a LOOKUP function that would return the column values (multiple rows) for results. like : LOOKUP ( $ID_Country , @1:1, @2:9)

What other function or trick can I use to solve this problem ?

 

Thank you very much for your help.

Date Votes

4 comments

  • 0
    Avatar
    Meggan King

    Hi Eva -

    We will need to have a look at your data to suggest the best solution here. I've opened up a support ticket from this post and the team will reach out. Once we have a nice solution we can come back to this post with our suggestions. 

     

    Thanks

    Meggan

  • 0
    Avatar
    Jo Tivey

    I'm having the same issue, I can't find a simple way of doing this. My current solution is to use the Switch function and indicate for every option (so in this case, for each country you can select as your user variable) if it matches the case of the columns first row then switch the value to that of the corresponding column.

    ie:

    SWITCH($ID_COUNTRY,A1,A:A,B1,B:B,C1,C:C,D1,D:D,E1,E:E,F1,F:F)

    if all your country labels are in A1, B1, C1 etc and the corresponding data in A:A, B:B, C:C etc.

    The more choices you have as your variable, the messier this gets - I'm using months in a year so it's already got 12, depending on where I cut off the data it could get to be a very large formula! A more elegant solution would be much appreciated :)

  • 0
    Avatar
    Janice Janczyn

    Hi Jo,

    The most elegant solution would be to re-orient your data such that you can select data by rows, such as

           A           B           C

         date    country    data

     

    where you can filter/select column C based on the value in column B and/or A. With this approach, your formula doesn't need to be updated as countries and dates are added to your datasource. If this isn't possible, a lower maintenance approach (depending on your specific scenario) may be to use a different sheet per country, however that still requires you to re-organize your data. If you have further questions, please email support@klipfolio.com with your details.

    Thank you,
                  Janice

  • 0
    Avatar
    Jerry Hsieh

    Eva,

    I ran into this issue as well. We are using Google forms for a survey and the forms set the question as the column header. As such, reorienting the data wasn't an option. I could, however, add a second header row without preventing the survey responses from writing correctly. Not sure if this would work for you (maybe a hidden row in the sheet) but my solution was:

    1) Add a new row to the top of the sheet (GSheets in my case).

    2) In the cells across the top I added a formula that would return the letter value assigned to the column in the cell. The formula: REGEXEXTRACT(ADDRESS(ROW(), COLUMN()), "[A-Z]+")

    3) Set my input control to look to the first row for VALUES and second row for LABELS. This way when someone selects the items in cell "B2" from the input control menu the value assigned to the variable ($CellLetter for this example) is the cell's letter value from "B1" from the extraction formula.

    4) I used the DATASOURCE() and CONCAT() functions to select the contents of column B: 

    DATASOURCE("datasourceID",concat("SheetName,",$CellLetter,":",$CellLetter))

    (This is the equivalent of just clicking the column and selecting the full contents so I also sliced out the two header columns)

    Hope this helps someone else.

Please sign in to leave a comment.