0

As long as Gooddata don't work with hours, we created two attribute datasets (begin_hour and end_hour) to handle hours of the day (from 00 to 23).

We also have a Store dataset whitch have two fields to handle the working hours for each store (store_begin_hour and store_end_hour) as well.

In order to get values only from the working hours of each store, we made the following metric:

select avg(store_temperature) where begin_hour >= store_begin_hour and end_hour <= store_end_hour

Firstly it worked but we noticed that there was no data for some hours (15,16,17). Then we looked to the elements?id of each one of the hour attributes and we found the problem.

The element?id of these 3 hours (15,16,17) hadn't the same sequence of the others (... 13h=22, 14h=23, 15h=7, 16h=6, 17h=9, 18h=24 ...), so it wasn't included in the select criteria.

I've tried to delete the hour attribute values and full load the same data to these datasets again but the id's still the same after all.

There are some way to change these 3 id's for these 3 hours?

OBS: The "id" that I'm talking about isn't a field, it's the url identifier of each attribute value, like in the image bellow:

id of the attribute

Alvaro Prataviera Filho
  • 5
  • 4

3 Answers3

0

There is a way how to work with hours and minutes in GoodData using the custom date dimension.

But to completely answer your question it's necessary to investigate your logical data model. Could you please share it via support@gooddata.com ? Thx!

Jarda Machaň
  • 51
  • 5
  • It's really necessary to investigate my logical data model? I just want to know if I can change an specific attribute id and how to do it. – Alvaro Prataviera Filho Aug 23 '18 at 17:41
  • These attributes can't be changed, it's an internal platform feature. If you are loading the same data to one dataset it won't help because there are already some ids (when you are running out of ids, it's not generated through max+1 but blank spaces are being used). The way how to solve this is through "facts" and its comparison. I will investigate a detail for you asap. – Jarda Machaň Aug 24 '18 at 13:23
0

in general, numeric comparisons such as begin_hour >= store_begin_hour only works with numeric types, i.e. facts. The internal numerical identifiers of attribute values are automatically generated at the load time and cannot be changed by the end user.

If you want to use your custom time dimension for both slicing and numerical filtering, I recommend to extend it with additional facts (or maybe just want fact for minute of day).

Just for the sake of completeness - there is one exception of what I described in the first paragraph - the GoodData built-in date dimension. Its built-in date attributes have a dual attribute/fact character and they can be used for both slicing, aggregating and numeric comparison.

Hope it helps,

Pavel

Pavel Kolesnikov
  • 119
  • 7
  • Numeric comparisons such as ```begin_hour >= store_begin_hour``` worked here for me. Not as expected of course, but it worked. I didn't know the GoodData built-in date dimension, I'll search about this. Thanks for your answer! – Alvaro Prataviera Filho Aug 27 '18 at 21:39
  • Just to clarify - the built-in data dimension only includes daily granularity. You still need your custom dimension for hours and minutes. – Pavel Kolesnikov Aug 28 '18 at 22:06
0

I solved this problem by inserting these 3 hours (15,16,17) in the clausule.

Something like "select value where (hour between 08 and 18) or (hour in (15,16,17))".

I know that it's not good to make hacks like this but I had no other choice apparently.

Thank you guys for all the answers! You are awesome!

Alvaro Prataviera Filho
  • 5
  • 4