Best Of
Re: Marking Duplicates So I Can Filter Them Out
Ah sorry I missed that you want both the original and the duplicate row gone when a duplicate exists. Interested in your use case - why you want both records gone when there is a duplicate and not just the duplicate? Is it that the duplicate transaction is to zero out the previous? If that is the case, and `retail` is the field that is being zero'd out then I agree with @ST_-Superman-_ 's approach. Here is how I did it using the data you provided with the correct output you are looking for:
1. Overall ETL:
2. Group By step:
3. Filter Rows step
4. Output - Only unique rows
Summary Numbers - Adding Commas
Hi,
I wasn't able to find out much about this searching the DOJO, so hopefully this could be useful to everyone.
I've created a CASE statement using a very simple algorithm to add in comma seperators when you're trying to CONCAT multiple values in the Summary Numbers.
CASE
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 12
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 4, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 7, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 10, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 11
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 2)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 3, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 6, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 9, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 10
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 1)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 2, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 5, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 8, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 9
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 4, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 7, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 8
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 2)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 3, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 6, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 7
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 1)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 2, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 5, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 6
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 3)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 4, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 5
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 2)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 3, 3)
)
WHEN LENGTH(CONCAT (
''
,ROUND(Sum(`A`), 0)
)) = 4
THEN CONCAT (
SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 1, 1)
,','
,SUBSTRING(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 2, 3)
)
ELSE RIGHT(CONCAT (
''
,ROUND(Sum(`A`), 0)
), 3)
END
Enjoy!
Warm regards,
Marc H.
Marc_H
allow users to duplicate (save as) locked cards
Hello,
My goal is to have published reports be locked down, but accessible to users to manipulate unofficial copies of them.
I would like to lock pages and cards so that only a users assigned to a group that owns the card or page (or is an admin) can edit them, but want all users with a user role that allows them to edit cards to be able to duplicate (save as) the card to their own overviews so that they can manipulate them. does anyone know if this is possible? this resource suggests that it might not be possible.
I need to be able to prevent changes to official cards and pages, but want users to be able to interact with the content.
Zach13
Re: Performance of private embedding
Thanks for your suggestions. Yes we had cached the access token and that does improve things a bit but on the second call the authentication is slightly quicker anyway.
The dataset is quite large but the board is much faster when accessed within the instance itself. Possibly the programmatic filtering slows down the load when embedding?
I think we'll lazy load it like you have suggested and then make it visible (hopefully can force the cards to refresh when we do this). I also hope the cards all render ok in an hidden iframe. Worth a try though.
I think we are in the same aws region as the domo instance.
Thanks again.
Jones01
Re: Identify Null Roll-Ups in a Pivot Chart
Here is a tutorial put together by @MarkSnodgrass with filling in missing dates.
Confused about the difference between pages, dashboards, and Domo Stories
Hi,
I can create a new page from the "manage pages" menu.
I can also use this other menu to create a Dashboard, which will also appear in the page listing:
I can also take a regular already-existing page and click on "Design Dashboard", which the docs say it will create a "Domo Story":
What's the difference between a Page, a Dashboard, and a "Story" created through "Design Dashboard"?
Are "Stories" full-blown dashboards, or are they something else?
(Edited to remove some incorrect paragraph about "filter views".)
danidiaz
Re: Calculating a % based on multiple dates
SUM(IFNULL(DATEDIFF(CURDATE(), 'Start Date'), 0)) / NULLIF(SUM(IFNULL(DATEDIFF('End Date', 'Start Date'), 0)), 0)
Re: Calculating a % based on multiple dates
A couple of questions:
- What is the answer you would expect to get when a Campaign has no start date?
- What should be the answer when a campaign has no end date?
In your dataset, what is the level of granularity you have? If this is a campaign level dataset, then a formula to cover the out of bond scenarios (no start date, not yet started, no end date and already ended) first, and then operate at the row level is likely best:
CASE WHEN `StartDate` IS NULL THEN 0 WHEN `StartDate` > CURDATE() THEN 0 WHEN `EndDate` IS NULL THEN 0 WHEN `EndDate` < CURDATE() THEN 1 ELSE ROUND(((UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0)/ROUND(((UNIX_TIMESTAMP(`EndDate`)-UNIX_TIMESTAMP(`StartDate`))/86400)-0.5,0) END
You could try with DATEDIFF but that one is known to behave oddly sometimes, so the UNIX diff is often preferred and more reliable, feel free to try both.
