Derive Calculated Field

[nihhalmca]

Hi All -

I need to derive calculated field based on condition. Attached document for detail explanation and sample data.

Please assist on this.

Thank you.

[justISO · Accepted solution]

Hi, not so elegant solution, but if you have 3 buckets with 'changeable' bucket days and rates, you can try something like this:

temp:
load * inline [
Key,	StartDate,	EndDate,	Days
abc,	2022-07-30,	2022-08-02,	3,
lmn,	2022-07-20,	2022-08-28,	8,
xyz,	2022-07-15,	2022-07-27,	12,
]; 

LEFT Join
load * inline [
Key, 	Bucket1Days, Bucket1Rate, Bucket2Days, Bucket2Rate, Bucket3Days, Bucket3Rate
abc,	5,	10,	10,	20,	15,	30,
lmn,	5,	10,	10,	20,	15,	30,
xyz,	5,	10,	10,	20,	15,	30,
];

table:
load 
Key, StartDate,	EndDate, Days,
if(Days<=Bucket1Days, Days*Bucket1Rate,
if(Days<=Bucket2Days and Days>Bucket1Days, (Bucket1Days*Bucket1Rate) + ((Days-Bucket1Days)*Bucket2Rate),
if(Days<=Bucket3Days and Days>Bucket2Days, (Bucket1Days*Bucket1Rate) + ((Bucket2Days-Bucket1Days)*Bucket2Rate) + ((Days-Bucket2Days)*Bucket3Rate)
))) as CalcField
RESIDENT temp;
drop table temp;

[nihhalmca]

It's working, thank you.