Best Of
Re: Using RegEx to Extract Date
Try this:
REGEXP_REPLACE(`string`, '^.*(\d{2}-[A-z]{3}-\d{4}).*$', '$1')
You need to replace the entire string not just the substring. This is why there's ^.* and .*$ to match anything before and after the date value respectively.
Re: Counting rows with a specific numerical value when using 'Group By'
Count is counting the number of non null values. You’ll want to return a value only if you want it counted. You can use a CASE statement in your COUNT to do this
COUNT(CASE WHEN `ptstatus`=80 THEN `ptstatus` END)
Re: Magic ETL | Remove Duplicates Old Raw
You can use a group by tile to select the MAX Date grouping on your ID then do an inner join where the Date and ID match to get the most recent records in your ETL.
Re: API Accounts
- Are you referencing creation of these on developer.domo.com? These don't have the Domo Grants applied to them.
- To use an API account the account would need to be either created by the user so they're the owner or have it shared with them. If they want to create a new account and one doesn't exist they'd need the Create dataset account permission. They would need Edit dataset if they're attempting to update or modify the dataset configurations (i.e. changing the account id)
- Manage all accounts means that user can see and modify any accounts in the system even if they aren't the owner or have it shared with them. Create dataset account means that a user is allowed to create a new API account but may not be able to view other accounts shared with their user.
Re: Capture Duplicates in DOMO
Hi @WorldWarHulk you could make a beast mode like this to count the same name and title appear in the same row:
COUNT(COUNT(`Location`) FIXED (BY `Name`,`Title`))
Re: I need to create a dynamic report that shows me data between 2 and 5 days ago
Hi @Mike_Shrift ,
One thing you can do is to make a filter. So if you have a card that is a table, you can write a formula/beast mode like
CASE
WHEN `date`>=(Curdate()-5) AND `date`<=(Curdate()-2) then 1 end
Then you'd filter on 1s.
Make sense?
John Le
You're only one dashboard away.
More video solutions at: https://www.dashboarddudes.com/pantry
Re: Capture Duplicates in DOMO
See if either of these works:
sum(count(`Location`) fixed (by `Name`,`Title`))
or
count(count(`Location`)) over (partition by `Name`,`Title`)
Re: I'm stuck. Sum of a Windowed/Partitioned Sum???
Hi @chapman
So I think you're close, but just need to move some things around. I made a video for you to show you how:
https://www.loom.com/share/295fd98f101c4ef0b5a6a7e6ddce4363
Good luck,
John Le
You're only one dashboard away.
More video solutions at: https://www.dashboarddudes.com/pantry
Re: how to the find difference between dates ( in days ) that are in one column?
can you try:
DATEDIFF(`Transaction Date`,LAG(`Transaction Date`,1) OVER (PARTITION BY `Customer ID` ORDER BY `Transaction Date`))
I'm not sure if LAG() works in beastmode or not