I use Sum(RangeSum(v)) to fix this. Is this the right way?
2. Duplication of values:
My final table has the value from T1 (correct), sum of T1 and T2 instead of just sum(T2), and the single values of T2 with s not set. I can fix this by subtracting the value of T1 and filtering the single values. But how comes???
3. As in my sample there are ID which just exists in T1, and I want to omit them.
I need something similar to the SQL "Exists".
Here m actual code:
[PipeLine]: Load PID, Sum(PD_to_deliver) as PD, 'Contracted' as Status Resident deals Where status='won' Group By PID;
Load PID, Sum(RangeSum(PDB)) as PD, 'Delivered' as Status Resident Booking Group By PID;
Hi, I don't know what you mean with "missing values" but I don't think that rangesum in that way will solve it. Neither I understand the "and the single values of T2 with s not set" part
Just to guess a solution you can work with:
// first LOAD table2 to filter table1 after this one using Exists()
Sum(RangeSum(PDB)) as PD,
'Delivered' as Status
Group By PID;
Sum(PD_to_deliver) as PD,
'Contracted' as Status
Where status='won' and Exists ('PD', PD_to_deliver)
Group By PID;
Sum(If(Status='Contracted', PD)) as PD_T1, //Table 1?
Sum(PD) as PD_Total, // Sum of table 1 and table 2
Concat(If(Status='Delivered', PD), ', ') as PD_SingleT2
Group by PID;
DROP Table tmpPipeline;