ETL Rank&Windowでの累積列追加

eriena

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

 

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

jaeW_at_Onyx

"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.

jaeW_at_Onyx

@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

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.

jaeW_at_Onyx

"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.

eriena

Thank you for your advise!!

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