jhl

About

Username
jhl
Joined
Visits
0
Last Active
Roles
Member
Points
82
Badges
0

Comments

  • Valiant Spur, thanks for this - currently kicking myself because your explanation is so. much. more. elegant. than the (unecessary) rank and window stuff I described here. Even had the SQL in there, how did I forget about group by in ETL^^ JHL
  • Youre right, my mistake - you can ignore the rank and filtering (unless you have another column to set as the second ranking row). Instead, you can use ETL function "remove duplicates" as a last step before creating the output dataset to remove any duplicate dates you have - because the average will be the same in all…
  • Hi, sure,I'll try. 1) use "set column type" on your datetime column to make it a date (without a timestamp) 2) use "rank and window" for two functions: -Use "rank" (ascending) using the "add function" - you will have to give your column a name. - use "average" on your Value column (whatever you are actually measuring) -…
  • Hi, I think what you need is a window function - Beast Mode does not support it, unfortunately. However, Magic ETL does. (There is a chance that it works in SQL too using Select `Date`, AVG(Value) from source GROUP BY `Date` but I havent tested this) You will need to use your date as a partition - maybe you will need to…
  • Hi, if what I think you want to do is what you want to do (calculate number of minutes between two timestamps) then I do not entirely understand why you cant just delete the first *60 part to get the right result? You definitely cant use the same code for this. Two different query languages return different results when…
  • Hi, I am not sure what is going on with the join, the inner join adds only rows that have a matching identifier in both datasets, I dont know the definition by heart, but it might just be that it matches multiple times because it is a constant... 1k x 1k = 1m. But there is an easier way than matching on your additional…
  • Have you tried just multiplying the number by -1 using beast mode? This would mean that the shortest times are the "largest" words. Depending on your original data type you might have to try reformatting it to for example, a decimal field so it can actually by multiplied with anything (if it is just a decimal with, say,…
  • Hi mylee, sorry for not being entirely clear if you just set up one beast mode like case when `Country` LIKE '%Poland%' then 'Poland' when `Country` LIKE '%Sweden%' then 'Sweden' when `Country` LIKE '%France%' then 'France' when... then... end and put as many "when... then..." statements into the beast mode as you need,…
  • Hi Lee, you can try the LIKE operator in a beast mode calculation, such as case when `Country` LIKE '%Poland%' then 'Poland' end repeating the 'when... then' part for each country you have in your list (or may need) and then setting a filter on the calculated field. The '%' here serves as the placeholder to make sure it…
  • I do not think this is possible using the existing color rules in analyzer. However, you might want to take a look at Adding Graphics, Links, and Images to Table Cards Using Beast Mode from the knowledge base. The same logic (using CONCAT in beast mode to insert html code) may apply and help you achieve what you want. HTH
  • Always happy to help ?
  • I noticed that in some of our graphs it seems to be called "is always" now.
  • I just fiddled around with another dataset, try setting one of the color rules to "contains any" - this will get rid of the duplicate color and your labels apply.
  • Is there another color rule applied to the dataset? 
  • Do you have a color rule for Total Minutes late other than the >10 or <=10 minutes? If there is one that "contains any" in your color rules, it will display even if it is not in the chart. (the question is also why you would want this, judging from your chart everyone but two is late :p)
  • If I am understanding this correctly, you are not sure about the type of join you are using? Just FYI, you can specify the type of join to use in Magic ETL by clicking on the symbol between the two datasets you will see when editing the "Join Data" step.