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

[PrashantSangle]

Hi,

try below script and run

Test:
LOAD * INLINE [
    ID, Time Difference, Reference
    1, 10.50, 0
    2, -, 1
    3, 15.45, 0
    4, -19.05, 0
    5, -, 2
    6, -, 1
];

JOIN
LOAD MIN([Time Difference]) AS MIN_TIME,
MAX([Time Difference]) AS MAX_TIME
RESIDENT Test;

final:
Load ID,
IF(Reference=1,MAX_TIME+1,if(Reference=2,MIN_TIME-1,[Time Difference])) as new_Time_diff,
Reference
Resident Test;

drop table Test;

Regards,

[#3]

Hi Max,

Is there no other way to do this,

by using some variable and using it.

Regards,

Anjali Gupta

[PrashantSangle]

Hi,

Yes you can use variable.

where you want to use variable in script or in front end expression.

Try below in above suggested script then no need to join the table and after setting value in variable you can drop table.

let varMinTime=Peek('MIN_TIME');

let varMaxTime=PEEK('MAX_TIME');

Regards

[sunny_talwar]

Why do you want to add variables? You can save the Min and Max in a variable, but that would be just adding another step to the process.

[#6]

I want to use it in script itself.

Could you please guide me again on how to use the code.

Thanks and Regards,

Anjali Gupta

[sunny_talwar · Accepted solution]

Modifiying max's code:

Test:
LOAD * INLINE [
    ID, Time Difference, Reference
    1, 10.50, 0
    2, -, 1
    3, 15.45, 0
    4, -19.05, 0
    5, -, 2
    6, -, 1
];

Temp:
LOAD Min([Time Difference]) as MIN_TIME,
Max([Time Difference]) as MAX_TIME
RESIDENT Test;

LET vMin = Peek('MIN_TIME');

LET vMax = Peek('MAX_TIME');

DROP Table Temp;

Final:
Load ID,
IF(Reference=1, $(vMax)+1,if(Reference=2, $(vMin)-1, [Time Difference])) as new_Time_diff,
Reference
Resident Test;

drop table Test;

[Kushal_Chawda]

Please see the attached

You can achieve it using variables in front end

[#9]

Hi Sunny,

I want to use it like :

vMaxDifference= Max(TimeDifference)

vMinDifference= Min(TimeDifference)

then use in script as:

if Reference=0,TimeDifference

if Reference=1,vMaxDifference+1

if Reference=2,vMinDifference-1 as NewTimeDifference.

as using max(TimeDifference) will be based on particular ID and not the whole.

[sunny_talwar]

So each ID will have it's unique Min and Max?