Dimension referencing other view won't work

I’ve got two views for this, an account list, and a list of arrangements.
The model these are in have a FROM: of the account list, with the arrangements view joined.

The account list view is vw_account_list, from the view vwAccountList in our SQL, and has a label of “Account List”.

Because I need to count some of these arrangements with different filters, I went to set up a Dimension, that includes a CASE WHEN, part of which is:

${account list.statusname} =

I tried various vw_account_list instead, but it looks like the correct syntax is “account list” since that’s the only one the validator allowed me to use.

When I try and run an explore using this field I get an error that the from the SQL end, and looking at the SQL it still says

${account list.statusname} =

And it’s the only field in the entire SQL code that includes the $ { } part.
Looking at the SQL, it looks like I need to change “account list” to the name of the Model, since the FROM part is looking at the account list view, giving it the name of the model.

If I change the LookML in the arrangement table to use the name of the model though, it tells me it’s an unknown or inaccessible field.

I know I’m doing something wrong, or just missing something, could someone please point me in the right direction on how I reference the main view of a model in another view?

I dont like to use the from: parameter on the base view of an explore. It’s always better idea to use view_name: and then reference the fields normally with the actual view name.

In my experience, this resolves most of these confusing aliased naming problems.
Does that fix it?

I might need some guidance on how to do that.

I went into the model, and typed in view_name and the quick helped suggested it works the same as from, so I just switched from to view_name. The model still seems to work in all other regards, but I’m still getting the same SQL error when I try and use the new field.

Now the SQL is saying the account list view is named “vw_account_list” instead of the model name, so it feels like some progress.

So now:

  1. SQL is naming the main table as “vw_account_list” which is the name of the view in Looker.
  2. In the LookML of my arrangement view, it won’t let me use “vw_account_list” it will only let me use “account list” without the validator throwing up an error. This is the “label” of the vw_account_list view.
  3. The SQL is still looking for ${account list.statusname} and throwing up an error.

I know it’s probably me doing something wrong, but it feels like the LookML Validator is saying it has to be named one way, but when Looker is running the SQL it needs to be named the other way.

This stuff is hard to conceptualize without seeing examples— Could you maybe post a truncated snippet of your explore to see exactly how things are being referenced?

This docs section talks about their behaviors: https://docs.looker.com/reference/explore-params/view_name#view_name,_from,_and_label_are_often_confused_but_have_different_behaviors

This is the relevant section that I find extremely helpful usually:

explore: customer {
  view_name: user
}
# Would appear in the Explore menu as "Customer"
# Fields would appear like "User Name"
# You would reference fields like ${user.name}

explore: buyer {
  view_name: user
}
# Would appear in the Explore menu as "Buyer"
# Fields would appear like "User Name"
# You would reference fields like ${user.name}

I realize I haven’t explicitly answered any of your questions, but I think after seeing the example explore I might be able to help more clearly.

The main table is the account list, this should have 1 row/record per account
image

A second table (there are more, but for this I’m only looking at one, is a complete history of every arrangement the account has been on:
image

These are joined in the model:
image

Now what I need, is to be able to count the number of arrangements that are broken (indicated in the Arrangement view) but only when the status on the account list is Broken Arrangement. This gives the number of arrangements currently broken, rather than the complete number of broken arrangements ever

The problem I have in that dimension, where it says “account list” this is the “label” from my first screenshot, the LookML validator won’t let me use “vwaccountlist” or “vw_account_list” or “account_list” or anything else other than “account list”.

Since that’s the only valid one, I save it and go to an explore and try and use the new measure, and I get an error which shows the code as:

So the problem clearly looks to me like it’s incorrect handling the account list table name in the arrangement table, that the LookML is insisting on one thing, but the SQL doesn’t understand it, the account list table is being given the model name, which isn’t used in the select.

If I instead use “view_name” other than “from” it looks a little better in the SQL, but still has the same problem

I must be doing something incorrect, but the CASE in the Arrangements view won’t let me use anything other than “account list” or gives me an error:
image

Hopefully this is enough information (and I apologise for my SQL view name missing an N)

I’m not sure where account_list is coming from, since I don’t see it referenced anywhere. Is there a view actually called account_list? I think the problem is that since there’s no view called account_list, it’s inserting ${account_list.statusname} literally into the sql— But if you instead say

explore: hangar_sheffield {
view_name: vw_account_list

join: vw_payment_arragements {
relationship: one_to_many
sql_on: ${hangar_sheffield.rmr_id} = ${vw_account_list.rmr_id} ;;
}

and then, in the arragement view, define your dimension as

dimension: currently_broken {
sql: CASE WHEN ${TABLE}.arrangementstatus like 'Broken%' and ${vw_account_list.statusname} = 'ZERA\Broken Arrangement' THEN "Yes" ELSE "No" END ;;
hidden: yes
}

it ought to work. I tested out this pattern on my instance and didn’t get a lookml error or a SQL error on running.

If you use view_name in the explore, and vw_account_list in the view, I think you will be set. Give that a go?

There is no view called account list, the only place this is referenced is the label of “vw_account_list”.

If I change “from” to “view_name” I also need to change the sql_on line, so it looks like:
image

I’ve saved and validated it, but not pushed to production.

I then change the dimension to:

But I still get the error:
image

I tried some other links, and I actually found the problem was “statusname” should be “status_name”, which the error didn’t indicate at all. Changing this let me save it, and let me run the report I was looking at fine.

The new problem now (and I haven’t committed any of these changes yet) is I can’t seem to run anything from the account list table anymore?

It’s like it can’t recognise that the account list view exists anymore, and won’t use any of it’s fields:


See, it’s not even adding in the filters, and when I add in the fields they’re all blank.
Maybe it’s something to do with needing to commit everything and push to production? But I don’t see why it would be that.

A lot of strange stuff going on here! Just to clarify, you’re running these queries in dev mode too, right? So the fact that you haven’t pushed to prod wouldn’t be an issue?

I hate to admit defeat, but since you’re over in EMEA and I’m out in California, you might want to just hop on chat during your daytime— The geniuses out in Dublin will be able to figure it out, I bet! Sometimes it just takes that real-time back and forth.

PLEASE keep updating, though! Happy to keep lending my opinion and I do want to know what eventually becomes of this.

Since I needed to change something else I reverted all these changes yesterday.
This morning went on to replicate it, and everything works fine. I have no idea if I did something wrong or not, but it’s working fine now.

I was running the queries in dev mode. I’ve since pushed this to production, managed to double check that the measure I was trying to get in the first place looks like it works, and also to be sure got someone else to run a couple of views to make sure the account list hadn’t disappeared on them.

So the entire thing looks solved now! Thanks!

I take it back! I don’t admit defeat!

Glad to hear it’s solved now. Since it took reverting to fix it, I have a hunch that you must have mixed something up innocuous looking that you’d never have thought to un-mix… Maybe check out the diff between those commits, could be interesting.

But hey, you fixed it! Nice job, Paul. :slight_smile: