topic Re: Using variable in script in QlikView

Using variable in script

Tue, 29 Dec 2015 10:13:50 GMT

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

Re: Using variable in script

PrashantSangle — Tue, 29 Dec 2015 10:28:13 GMT

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,

Re: Using variable in script

Tue, 29 Dec 2015 10:38:58 GMT

Hi Max,

Is there no other way to do this,

by using some variable and using it.

Regards,

Anjali Gupta

Re: Using variable in script

PrashantSangle — Tue, 29 Dec 2015 10:48:49 GMT

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

Re: Using variable in script

sunny_talwar — Tue, 29 Dec 2015 10:51:28 GMT

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.

Re: Using variable in script

Tue, 29 Dec 2015 10:51:52 GMT

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

Re: Using variable in script

sunny_talwar — Tue, 29 Dec 2015 10:54:26 GMT

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;

Re: Using variable in script

Kushal_Chawda — Tue, 29 Dec 2015 10:54:30 GMT

Please see the attached

You can achieve it using variables in front end

Re: Using variable in script

Tue, 29 Dec 2015 10:56:12 GMT

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.

Re: Using variable in script

sunny_talwar — Tue, 29 Dec 2015 10:57:42 GMT

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

Re: Using variable in script

Tue, 29 Dec 2015 11:11:15 GMT

Thanks Max and Sunny

Regards,

Anjali Gupta

Re: Using variable in script

sunny_talwar — Tue, 29 Dec 2015 11:12:00 GMT

We are glad we were able to help

Re: Using variable in script

Tue, 29 Dec 2015 11:12:24 GMT

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.

Re: Using variable in script

Kushal_Chawda — Tue, 29 Dec 2015 11:21:25 GMT

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;

Re: Using variable in script

Tue, 29 Dec 2015 11:25:34 GMT

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

Re: Using variable in script

Kushal_Chawda — Tue, 29 Dec 2015 11:28:10 GMT

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

Re: Using variable in script

Tue, 29 Dec 2015 11:29:19 GMT

ok. Thanks Kush for the knowledge.

Re: Using variable in script

Kushal_Chawda — Tue, 29 Dec 2015 11:30:13 GMT

Yeah enjoy