Best Of
Re: Is there a way to force a column header to be blank?
@Stuck You'll need to use the line break tag from HTML. If you put <br> as the data label, it will make the header blank.
Re: Workaround to Pivot tables pushing non-valued categories to the bottom.
@Stuck A couple other things to consider:
- Does your custom sorting beast mode take into account the null handling used in your value field?
- If you have multiple sort fields in your card, are you able to achieve the desired behavior by rearranging the order that the fields are listed?
If neither of these get you what you need, then I'd suggest posting a screenshot of your analyzer settings so we can dig into it further.
Re: Workaround to Pivot tables pushing non-valued categories to the bottom.
@Stuck Can you elaborate on how you "force sorted"? You can create a beast mode to create a custom sort order like below:
case when X then 1 when Y then 2 when Z then 3 else 4 end
Also, can you check that the aggregation used in your sort fields aligns with the aggregation of the same fields in the pivot table? If any of your sort values are set to "No aggregation", then that could be causing issues as well.
Re: Workaround to Pivot tables pushing non-valued categories to the bottom.
@Stuck To clarify, by "non-valued" do you mean the the value is zero, null, or an empty string? Regardless, you can get around this by hard-coding a value using a CASE or IFNULL function.
That's probably not the answer you're looking for, but this is by far the simplest way to accomplish what you need. You can still distinguish which values you're overriding based on the value you hard-code in while preserving functionality.
Re: Workaround to Pivot tables pushing non-valued categories to the bottom.
@Stuck Of the options you're testing, SUM(IFNULL(`value measure`,0)) should give you the best result. The reason your case statement didn't work is because you had aggregate functions inside the case statement, which can cause sorting issues. Whenever you need to use an aggregate and a case in the same function, it's best to put the case inside the aggregate like this:
sum(case when `value measure` is null then 0 else `value measure` end)
Re: I am trying to remove duplicates so that I only count the result of the last batch run
@user028582 Here is a knowledge base article that shows how to use the Rank & Window tile and Group by in Magic ETL: https://domo-support.domo.com/s/article/360044876094?language=en_US
Either method will get you what you need, so let us know which one you want to try and we can help answer any other questions.
Re: Domo Account Attributes
@faisalnjit That's correct. If you need to make changes, then your colleague who created the account must do it. Otherwise you would have to create a new account yourself and transition all existing datasets to the new account. In general I recommend creating a dedicated "Account Owner" user to own all dataset accounts.
Re: I am trying to remove duplicates so that I only count the result of the last batch run
@user028582 Rather than using a formula, I'd recommending using a Row Number function in the Rank & Window tile to identify the last batch (order by last batch date, descending). Then you can use a filter tile to only include data where the row number = 1.
Re: I am trying to remove duplicates so that I only count the result of the last batch run
Add a constant to your dataset and call it grouping column and set it to 1. Then feed that into a group by tile and group based on the grouping column. Select the MAX of the _BATCH_LAST_RUN_ field. Then use an inner join to group that result back to the original dataset where the two dates match.
Re: Can someone help with a simple join?
@damen For the null values, would you mind just posting the Loan ID and the Application_Key before the alter columns for a couple of those rows? It looks like it would be an issue where the Application_Key doesn't exist in the Powerlender dataset or isn't matching up to the Loan ID.