Creating a flag dimension based on a nested query

[Jacob_Weig]

Hi, 

Not sure what solution is best whether directly in the load editor or in the front end but basically I want to create a flag for when the sum by id is equal to 0.

This is what I would do in sql:

/*first filter id where sum per id is 0*/

with cte as
(select id
from my_table
group by 1
having sum(paid_amount)=0
)

select * , case when b.id is null the 1 else 0 end as cero_flag
from my_table a
left join cte b on a.id=b.id

[tensini]

Table:
LOAD
id,
paid_amount,
fields...
FROM source;

Left Join (Table)
LOAD
id,
If( sum(paid_amount)=0, 1, 0) as zero_flag
RESIDENT Table
Group By id;

[Jacob_Weig]

It worked perfectly, thank you!!