Grouped Data: type mismatch
Hi,
I am trying to create some data groups in report builder. The data I am trying to group is from a freehand SQL calculated field which is the result of a datediff function on two timestamps in the dataset (in minutes). The result is a numeric type but I want to group into things like "1-2 hours", "2-4 hours" etc
I can create the groups just fine but the query generated fails as the opening and end clauses in the CASE WHEN statement are the freehand SQL function itself, which returns a numeric value while the rest are the group labels, which are text.
It seems that Snowflake does not like this arrangement as it returns an error saying that the group labels are not a numeric type. Testing on DBFiddle suggests that Postgres behaves the same however MySQL seems to not mind
Is there anyway to alter the opening and end clauses or otherwise change how the generated SQL behaves? I tried putting the statement as it's own freehand SQL field, which allows the query to run but the sorting of the data thereafter is based on the text values and not the underlying data
Below is a snippet of the statement being generated, for reference
SELECT DISTINCT
CASE
WHEN 1 = 0 THEN datediff(minute, timestamp_a, timestamp_b)
WHEN datediff(minute, timestamp_a, timestamp_b) <= 10 THEN '10 Minutes'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 10 AND 30 THEN '10-30 Minutes'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 30 AND 60 THEN '30-60 Minutes'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 60 AND 120 THEN '1-2 Hours'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 120 AND 360 THEN '2-6 Hours'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 360 AND 1440 THEN '6-24 Hours'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 1440 AND 2880 THEN '1-2 Days'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 2880 AND 8640 THEN '2-6 Days'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 8640 AND 20160 THEN '1-2 Weeks'
WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 20160 AND 40320 THEN '2-4 Weeks'
WHEN datediff(minute, timestamp_a, timestamp_b) > 40320 THEN '4 Weeks+'
ELSE datediff(minute, timestamp_a, timestamp_b)
END AS C1,
The same problem 
Hi Dean,
Thanks for reaching out. Unfortunately, this is a known defect in Snowflake. I've gone ahead and added you to the client list of those being affected by this in the existing logged Defect. There is no workaround listed in the ticket, I'm afraid.
Any potential updates regarding this will be posted here. The task is a bit old now, so I've explicitly requested an update as well.
Regards,
Mike
Hi Dean,
Thanks for reaching out. Unfortunately, this is a known defect in Snowflake. I've gone ahead and added you to the client list of those being affected by this in the existing logged Defect. There is no workaround listed in the ticket, I'm afraid.
Any potential updates regarding this will be posted here. The task is a bit old now, so I've explicitly requested an update as well.
Regards,
Mike
Thanks Mike
I've found a bit of workaround myself
I adjusted the above CASE statement so that it returns all text values and added the whole bit as a freehand SQL calculated field (as I did before). Then I created another calculated field which returns just the results of the function itself. I then grouped this data in the exact same way but for the labels I put in numbers rather than text
The funny thing is that the SQL statement generated has those numbers as text i.e. '1' but Snowflake is happy to take them. In order to sort properly, anything after 9 is set as 99, 999 etc and Yellowfin then sorts in the order I want
It's a little messy but it does the job. Ideally we'd want the native functionality to work, especially if we wanted a lot more groups so thanks for adding us as an affected client. If it helps with the priority, as I mentioned it appears the same is true for Postgres and I would suggest it might be the same for Redshift as that is a fork of Postgres (although heavily modified). I don't have access to Redshift anymore so I can't confirm
Thanks
Dean
Thanks Mike
I've found a bit of workaround myself
I adjusted the above CASE statement so that it returns all text values and added the whole bit as a freehand SQL calculated field (as I did before). Then I created another calculated field which returns just the results of the function itself. I then grouped this data in the exact same way but for the labels I put in numbers rather than text
The funny thing is that the SQL statement generated has those numbers as text i.e. '1' but Snowflake is happy to take them. In order to sort properly, anything after 9 is set as 99, 999 etc and Yellowfin then sorts in the order I want
It's a little messy but it does the job. Ideally we'd want the native functionality to work, especially if we wanted a lot more groups so thanks for adding us as an affected client. If it helps with the priority, as I mentioned it appears the same is true for Postgres and I would suggest it might be the same for Redshift as that is a fork of Postgres (although heavily modified). I don't have access to Redshift anymore so I can't confirm
Thanks
Dean
Hi Dean,
Glad to hear you found a workaround! I've added this workaround to the task, but still explicitly stated that we get this working as expected considering the workaround is a bit messy, and is still ultimately just that: a workaround. I agree it likely to occur in Redshift as well and I've made note to test that in the course of looking at this as well, so thanks for the additional feedback!
I'll keep you posted with any progress made here.
Regards,
Mike
Hi Dean,
Glad to hear you found a workaround! I've added this workaround to the task, but still explicitly stated that we get this working as expected considering the workaround is a bit messy, and is still ultimately just that: a workaround. I agree it likely to occur in Redshift as well and I've made note to test that in the course of looking at this as well, so thanks for the additional feedback!
I'll keep you posted with any progress made here.
Regards,
Mike
Hi Lesley,
Seems you've taken this old one over so apologies for revisiting another thread today!
Just wondering if there has been any update on this defect?
It came back up for us yesterday when we were experimenting with Signals.
When exploring a signal and trying to open the Analysis section at the bottom, I kept getting that there was no explanation but looking at the queries, I could see they were failing and it was due to this same defect. However, since I've no control of the query being generated, I can't work around it
I'll be honest, we're just experimenting with signals for now and probably won't be using them in earnest in the short term but it is something we are definitely interested in and want to explore. I think the analysis/automated insights bit is crucial for our needs so I'd really like to see this defect fixed so we can get the most out of signals
Thanks
Dean
Hi Lesley,
Seems you've taken this old one over so apologies for revisiting another thread today!
Just wondering if there has been any update on this defect?
It came back up for us yesterday when we were experimenting with Signals.
When exploring a signal and trying to open the Analysis section at the bottom, I kept getting that there was no explanation but looking at the queries, I could see they were failing and it was due to this same defect. However, since I've no control of the query being generated, I can't work around it
I'll be honest, we're just experimenting with signals for now and probably won't be using them in earnest in the short term but it is something we are definitely interested in and want to explore. I think the analysis/automated insights bit is crucial for our needs so I'd really like to see this defect fixed so we can get the most out of signals
Thanks
Dean
Hey Dean,
Please do not apologise for chasing up on tickets - this is what they are here for! :)
I have just checked the development task and there has been some discussion around this and I can see it has been assessed - but there has not been any movement that I can see for a while.
I have also added a comment to the task chasing up on the status and when this will be looked at again - Once I have a response I know more on the progress of this, I will let you know, I hope that is ok !
Best Wishes,
Lesley
Hey Dean,
Please do not apologise for chasing up on tickets - this is what they are here for! :)
I have just checked the development task and there has been some discussion around this and I can see it has been assessed - but there has not been any movement that I can see for a while.
I have also added a comment to the task chasing up on the status and when this will be looked at again - Once I have a response I know more on the progress of this, I will let you know, I hope that is ok !
Best Wishes,
Lesley
Thanks Lesley!
Thanks Lesley!
Hi Dean,
Just wanted to let you know this fix has been included in our 9.8.2 release
you can find release notes and downloads here -
https://portal.yellowfinbi.com/public/releases/home
I'll mark this ticket as Resolved at this time.
Thanks,
Eric
Hi Dean,
Just wanted to let you know this fix has been included in our 9.8.2 release
you can find release notes and downloads here -
https://portal.yellowfinbi.com/public/releases/home
I'll mark this ticket as Resolved at this time.
Thanks,
Eric
Replies have been locked on this page!