Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Derived Tables Not Holding Values

  • 14 February 2018
  • 8 replies
  • 150 views
A

I have two tables Student and Marks.



I have created a view on Student applying a filter let say Student id.



Now I have created another view Marks, where the query is like:



derived_table: {


sql:


SELECT


name,


sub1,


sub2,


total


FROM marks


where name in (SELECT distinct name from ${student_extract.SQL_TABLE_NAME});;


}



The issue I am facing is when I am adding this in dashboard and applyting a filter on student id, the Student View gets displayed correctly, however the marks view displays all name. Why the derived table student_extract is not holding values only related to the student id?

8 replies

jesus.cruz
Rank

Hi @ayonsarkar . There could be a few reasons why this could be happening which I list below. But It may be easier to troubleshoot this if you send a new email to help.looker.com and include both of the derived table’s SQL and attach screenshots of your dashboard tile(s). This can help Looker Support Team investigate the behavior in more detail and understand what is being displayed on your end.



Possible Reasons





  • Is your dashboard tile listening to your dashboard filter in the “Tiles to Update” section



  • Do you see the expected behavior when viewing the Look/Explore pages? Or does this same behavior show across the dashboard/look/explore pages?


A

yes the same behavior shows across dashboard, look and explore. I have one of the dashboard tile listening to the dashboard filter which is Student Id. In the marks table I donot have student id, so if I try to apply filter on that tile it doesnot work.



I am currently working in a client network, so due to privacy issues I cannot share the screenshots.

jesus.cruz
Rank

@ayonsarkar Thanks for the update.



Are the Student and Marks files joined at the Model level? Or, on what view file is the student_id dimension field?



You may need to either:





  • Join the marks view with the view that has student_id. that way when you apply a filter on student_id, the results for marks will update accordinly


  • Or if student_id exists in Marks, define that field in the derived table and add that field as a dimension in Marks





For example:



derived_table: {


sql:


SELECT


name,


sub1,


sub2,


total,


student_id





....





dimension: student_id {


type: number


}
A

thnx Jesus…let me try this…what i have is this:



view 1:







  • view: student



    dimension: student_id {


    description: “”


    sql: ${TABLE}.student_id ;;


    }



    derived_table:


    sql: |


    select …


    from student


    where {% condition student id %} student_id {% endcondition %}


    group by 1







  • view: marks



    dimension: name{


    description: “”


    sql: ${TABLE}.name;;


    }







dimension: other_fields{


description: “”


sql: ${TABLE}.others;;


}



derived_table:


sql: |


select …


from marks


where name in (SELECT distinct name from ${student.SQL_TABLE_NAME})


group by 1



My model looks like this:



explore student {}



explore marks {}



Are you saying i will apply a join between student and marks at model level?

A

any updates?

jesus.cruz
Rank

Hi @ayonsarkar, thanks for providing the updated example. Regarding the relationship between the two views, you do not have to have them joined at the model level so long as you include: the Student view in the Marks view file.



The use of the parameter is interesting and is something I’ll review on my end to see if it would impact the expected results.



I’m also going to send you an email so that we can continue this thread over email to help with better replies/messages. Thanks.

A

thnx Jesus…please let me know…my email id is: ayon.sarkar@cigna.com

A

i tired to use include with in Marks view file, but I am getting all the rows for Student when I try to view in dashboard, this statement doesnot work:



name in (SELECT distinct name from ${student.SQL_TABLE_NAME})

Reply


Terms of Service