ETL Rank&Windowでの累積列追加

Dojo「仲間に相談」のトピックスでご指導いただき、ETLのRank&Windowで用いて累積列をもうけました。
月の順でSUMし、値が空白になったらその前までの累積したいです。
(列「Result」には0の場合は0と入りますので、空白になったらその後は必ず空白です)
Rank&Windowの④ではどのように設定すればよいでしょうか。

rank.png

 

よろしくお願いいたします。

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    "But if no value (as like null), I want to accumlate up to the previouse month and don't want to accumlate after that month."

     

    OH.  So you have the exact opposite problem I described.

     

    Create a FILTER WHERE Amount is not NULL.  Calculate the cumulative SUM.  then APPEND that data back to the rows where Amount IS NULL.

     

    Alternatively you could calculate the Max_Date where Amount is Not NULL and filter your data on Max_Date and then do your cumulative Sum.

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @eriena , i'm sorry, google translate is doing a horrible job translating your question.

     

    I'm assuming you're trying to create a cumulative sum and want to include one row per month even in months where there is no data.

     

    The cumulative sum (WINDOW & RANK) tile cannot generate months for you if there is no data, so you must LEFT JOIN your transaction table to a date dimension that has one row per month.

     

    you could use a date  dimension, apply a GROUP BY month transformation.  and then LEFT JOIN to your transactions.  

     

    That way you guarantee a row per month.

     

     

  • eriena
    eriena ⚪️

    Thank you for your reply.
    I'm not good at translating, but I let me try.
    I'm trying to accumulate the value of "Result" into "Result(Accum)" in order from Jan to Dec.
    If the value in "Result" >= 0, I want to accumlate in order.
    But if no value (as like null), I want to accumlate up to the previouse month and don't want to accumlate after that month.
    Is it possible?
    I try to set "null" as like this, but I couldn't solve.
    I need to accumlate several colums at once, so I can't use LEFT JOIN.

  • eriena
    eriena ⚪️

    Thank you for your advise!!

    Your advice inspired me! I could solve my question by using FILTER and appending them.

    ran3.png