can someone please help me for this question of magic etl

fadhlirrahman

Magic ETL Activity

Using the DataSet in Domo you created using Workbench in the previous activity,

1. Use Magic ETL to parse the currency code from the "Transaction Amount" column and separate the amount and the currency code into separate columns.

Submit: A screenshot of the output of the data showing the different columns created.

2. Calculate the average time between orders. For each record in the DataSet, add a field showing the number of days since the previous order for that customer.

Submit: A screenshot of which customer has the longest average time between orders and what is the average number of days?

so this is my practical quiz, I have no experience in this but i got a job as business intelligence and I have to finish this course, I have no problem with questions number 1 it is questions number I am very confused about.

this is how I do so far, I use the rank window to create the rank and another to Calculate the average time between orders. honestly, I don't know if I'm doing this correctly, can someone please let me know if what I did is correct? please? also how can I show which customer has the longest average time between orders and what is the average number of days?

ColemenWilson

@fadhlirrahman The next step would be to save and run the dataset and then create a card in which you can use a table card to show the longest average time between orders and the average number of days by using the DATE_DIFF() function. Let me know if you get stuck!

fadhlirrahman

i got questions for number 2 please anyone im so frustrate i search everywhere i cant understand

ColemenWilson

To see the time between orders for a customer you can use the rank and window tile with a lag function. Order by order date and partition on customer ID. Let me know if you are still stuck!

pauljames

@fadhlirrahman , For #2, I would investigate the rank & window tile. You must classify the 1,2,3,4,etc. order ranking for each customer ID. From there you can take the difference of days using date operations between the orders to then avg. I hesitate explicitly laying the answer out through snapshots as this is for certification, but this will definitely get you started in the right direction. :)

https://domo-support.domo.com/s/article/360042922814?language=en_US

fadhlirrahman

Hi it is solved, thank you

ColemenWilson

@fadhlirrahman you're welcome!

fadhlirrahman

i have managed to get the average time, it looked like this. but now im wondering on how to get A screenshot of which customer has the longest average time between orders and what is the average number of days?

do i use group by?

ColemenWilson

@fadhlirrahman The next step would be to save and run the dataset and then create a card in which you can use a table card to show the longest average time between orders and the average number of days by using the DATE_DIFF() function. Let me know if you get stuck!

Shannon

How were you able to Parse your text using the split column and separate the columns. Did you have to write a formula in the custom option?