I am having problem with the ‘is_search_source’ dimension in the challenge lab. That s the only one that’s unfinished and i dont know what mistake I am making? Anyone else completed this challenge lab?
1 Like
My codes:
TODO: Add dimensions and measures here
dimension: is_search_source {
type: yesno
sql: ${users.traffic_source} = “Search” ;;
}
measure: sales_from_complete_search_users {
type: sum
sql: ${order_items.sale_price} ;;
filters: [users.traffic_source: “Search”, order_items.status: “Complete”]
value_format_name: usd_0
}
measure: total_gross_margin {
type: sum
sql: ${order_items.sale_price} - ${products.cost} ;;
value_format_name: usd_0
}
dimension: return_days {
type: number
sql: DATE_DIFF(${order_items.delivered_date}, ${order_items.returned_date}, DAY) ;;
}
I’m not able to cross the next task: Join the view to an Explore
My codes:
join: order_items_challenge {
type: left_outer
sql_on: ${order_items.order_id} = ${order_items_challenge.order_item_id} ;;
relationship: many_to_one
}
But no success.
Would you share your solution here please? (so that we can explore further steps)
Blackraven, the key is on the hint:
Hint: you will need to add the order_id dimension inside of your order_items_challenge view to properly join it to the Explore.
Add this to your new view:
dimension: order_id {
type: number
sql: ${TABLE}.order_id ;;
}
And then in the join, you use order_items_challenge.order_id instead of “order_item_id”
1 Like
Also, your total_gross_margin will give an error once it’s correctly joined.
It should be using inventory_items instead of products! Big headache on that one on my side!
measure: total_gross_margin {
type: sum
sql: ${TABLE}.sale_price - ${inventory_items.cost} ;;
value_format_name: usd_0
}
1 Like
hi esteban, it works! Thank you!
However, I’m unable to complete Task 2. Create a persistent derived table
My codes:
training_ecommerce.model
join: user_details {
type: left_outer
sql_on: ${order_items.user_id} = ${user_details.user_id} ;;
relationship: many_to_one
}
user_details.view
view: user_details {
derived_table: { #this is line 6
explore_source: order_items {
column: order_id {}
column: user_id {}
column: total_revenue {}
column: age { field: users.age }
column: city { field: users.city }
column: state { field: users.state }
}
datagroup_trigger: training_ecommerce_default_datagroup #added
}
I get an error message:
Can’t construct persistent derived table user_details, connection bigquery_public_data_looker could not be registered
user_details.view:6
training_ecommerce:order_items
Do you know where’s the bug?
Hello!!
Glad it worked.
On that second step I think there’s an error with the lab.
What worked for me, with a similar code, was to disregard the error, go to the git console in looker and click on “Merge with remote” or “push to remote” or something similar. That didn’t show any success in the looker console, But after that I clicked the validation button in the lab and it showed Success!!
It’s a really weird situation. I understand the error is not on the code.
Try force pushing and even if it seems its not working, try to validate.
Hope it works for you too, but it happened the same for me some colleagues so it’s something general.
If it’s not working, maybe this is worth contacting someone from Looker support to help you past that step.
I finished the challenge lab and I’m currently working on the next badge.
Good luck!
hi esteban, update: I removed below line that was added previously, and it worked!
datagroup_trigger: training_ecommerce_default_datagroup #do NOT add this
Thank you so much for your help!
I have managed to complete this lab, but I think there’s a bug in the last 5 minutes.
See my solution: https://youtu.be/DP_oXW8MGcY
Hey!
Good to hear you could complete it.
What worked for me in the last step (I had the same issue as you)
Was to remove the old default datagroup, and also replace it with the new challenge datagroup in the persist_with in the model.
Once I did that the validation was successful. I think that might be the right way of getting the green check. But I agree in your case it was weird how you got success haha might be a bug.
1 Like
I am facing the same issue. No idea what’s wrong with the assessment
Hello Im having problems with the point:
- Next, add a filter to the Order Items Explore to only return data for orders that were shipped in the year 2018, unless a filter is applied to the Order Item Status or Order Item Delivered Date.
I create a sql_always_where filter nut the system dont check