Using variable in script

[#1]

Hi all,

I have a field name 'TimeDifference' which stores the difference in time interval.

But some of the fields are blank. How can i fill the blanks with some values based on another field Reference as below:

if Reference=0,TimeDifference

if Reference=1,max(TimeDifference)+1

if Reference=2,min(TimeDifference)-1 as NewTimeDifference.

ID	TimeDifference	Reference	NewTimeDifference
1	10.50	0	10.50
2	-	1	16.45
3	15.45	0	15.45
4	-19.05	0	-19.05
5	-	2	-20.05
6	-	1	16.45

How to get the max and min of TimeDifference?

Regards,

Anjali Gupta

[#2]

Thanks Max and Sunny

Regards,

Anjali Gupta

[sunny_talwar]

We are glad we were able to help

[#4]

Thanks for the reply Kush.. But i want to handle it on the back end, i.e. Script Level, and not on the front end.

[Kushal_Chawda]

Without variable also you can do it

Data:

LOAD * INLINE [

    ID, Time Difference, Reference

    1, 10.50, 0

    2, -, 1

    3, 15.45, 0

    4, -19.05, 0

    5, -, 2

    6, -, 1

];

Left Join

LOAD max([Time Difference]) as MaxTime,

min([Time Difference]) as MinTime

Resident Data;

Final:

LOAD *,

IF(Reference=1, MaxTime+1,if(Reference=2, MinTime-1, [Time Difference])) as new_Time_diff

Resident Data;

DROP Table Data;

DROP Fields MaxTime,MinTime;

[#6]

Thanks Kush but I have used what Sunny and Max have suggested and it worked great.

[Kushal_Chawda]

Yes That's not a problem. I am glad that it worked for you. It's just an alternative

[#8]

ok. Thanks Kush for the knowledge.

[Kushal_Chawda]

Yeah enjoy