Best Of
Re: ETL sometimes does bring information and in others it does not
@Rsandoval Does your output dataset actually contain 0 rows or does the dataflow history just show 0 rows updated? Depending on the schedule settings of your dataflow and Quickbase connector, it's possible that no rows were updated but the existing rows still exist in the output dataset.
If a dataflow is triggered and the input datasets have not updated since the last run, then MagicETL v2 will not reprocess the data because the results would be the same. In this case, the dataflow history will show the number of rows changed rather than the total number of rows in your output dataset.
Re: Removing Duplication and Ordering Objects within a Concatonated Field
@n____l____ Removing the "end" after your concat function should resolve the syntax error. If the nulls are still an issue, then you may need to update your 19 columns to show an empty string instead of null like this:
- case
- when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System'
- else ''
- end
Re: Removing Duplication and Ordering Objects within a Concatonated Field
@n____l____ Do you have a list of all possible products that could appear in the list? If so, you could use a formula tile in ETL to create a column to check whether each product is listed. For example, the column to check whether Student Information System is in the list would look like this:
case when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System' end
Then you can re-concatenate all the new columns in the desired order without duplication.
Re: Course Builder App loaded into Domo Format Issue
@MarkSnodgrass thanks!
@jnoel There have been some issues when viewing the card in a dashboard vs regular. Does it look the same if you look in full screen vs on a dashboard? Usually, when building in CB it should be in the same format that it will be in Domo.
Ashleigh
Re: Adding Hyperlink to table values
Never mind just figured it out, just needed to add the https:// in front. Thanks for your help!
mbrady
Re: How to create a column which SUMS based on the grouping of another column
You can use the group by tile to sum your quantity by invoice number and then join it back to your original dataset to get the total next to the individual items by joining back on invoice number. Your ETL would look something like this:
Re: Previous, Current, and Next Quarter Date Range in a chart
Ah okay. So I think you have to pick your poison.
1) Option 1: Make the artificial filter of a 1 if the quarter is a previous, current or future quarter, but then you can't use that same card if someone wants to choose a different quarter unless they open editor and change it
2) Option 2: You CAN make a filter at the top of your dashboard that is then defaulted to your artificial 1, but all cards on that dashboard will be affected by the date filter too if they are all from the same dataset. You can choose each card and turn off filter exceptions though so those cards aren't affected. Honestly, this 2nd option is a lot of work and I don't think worth it.
Why not just make a default card that has your artificial filter and another one right next to it that doesn't have the filter so someone can change it in a quick filter?
Re: Moved to beastmode section
@Katie_Forrest_2022 Your second beast mode is returning 0% because you are using a combination of OR and <> in you case statement, which is logically true for every row in your dataset. So for example, Discrepancy 121901 is not Discrepancy 121275, so it meets the criteria of your second exception.
I generally prefer to use additional WHEN... THEN... clauses in case statements over OR operators because it allows you to be much more surgical about classifying rows. It's also helpful that WHEN... THEN... clauses are evaluated top to bottom, whereas the order does not matter with criteria separated by OR operators.
I'd suggest structuring your beast mode like this:
count(distinct case
when `HOLD_NAME` in ('Discrepancy 121901','Discrepancy 121275', 'Discrepancy 121325','Discrepancy 121566')
then `Study Number`
when `HOLD_NAME` not in ('Incomplete Info' ,'PO – INVOICE HOLD', 'PENDING PO OVERRIDE' , 'CREDIT HOLD- INVOICE HOLD','CCCL Info Incomplete')
and `HOLD_NAME` not like '%Discrepancy%'
and `RETURNCODE` is not null
then `Study Number`
end) / count(distinct `Study Number`)
Re: Beastmode Including exceptions to % calculation
@Katie_Forrest_2022 Your second beast mode is returning 0% because you are using a combination of OR and <> in you case statement, which is logically true for every row in your dataset. So for example, Discrepancy 121901 is not Discrepancy 121275, so it meets the criteria of your second exception.
I generally prefer to use additional WHEN... THEN... clauses in case statements over OR operators because it allows you to be much more surgical about classifying rows. It's also helpful that WHEN... THEN... clauses are evaluated top to bottom, whereas the order does not matter with criteria separated by OR operators.
I'd suggest structuring your beast mode like this:
count(distinct case
when `HOLD_NAME` in ('Discrepancy 121901','Discrepancy 121275', 'Discrepancy 121325','Discrepancy 121566')
then `Study Number`
when `HOLD_NAME` not in ('Incomplete Info' ,'PO – INVOICE HOLD', 'PENDING PO OVERRIDE' , 'CREDIT HOLD- INVOICE HOLD','CCCL Info Incomplete')
and `HOLD_NAME` not like '%Discrepancy%'
and `RETURNCODE` is not null
then `Study Number`
end) / count(distinct `Study Number`)
Re: Broken Trend Chart With No Data
I have learned what I did wrong. Even though I did join the calendar, I had to add Group BY to the data to correctly count the number of items per day so that the chart can show 0 if I had no items in a month. I will mark my question as resolved.
Vova