topic Re: Set variable using FirstSortedValue in load script in QlikView

Set variable using FirstSortedValue in load script

wdchristensen — Thu, 12 Apr 2018 17:22:22 GMT

I am trying to get the syntax correct so I can get the min and max dates into variables using the FirstSortedValue function. Any help would be greatly appreciated!

Example:

LOAD *, 1 as ExampleFlag INLINE [

ExampleKey, ExampleDate, ExampleCountry

1000, 2018-04-12, Afghanistan

1001, 2018-04-11, Albania

1002, 2018-04-12, Algeria

1003, 2018-04-13, Andorra

1004, 2018-04-14, Angola

1005, 2018-04-15, AntiguaAndBarbuda

1006, 2018-04-16, Argentina

1007, 2018-04-17, Armenia

1008, 2018-04-18, Aruba

1009, 2018-04-19, Australia

1010, 2018-04-20, Austria

1011, 2018-04-21, Azerbaijan

1012, 2018-04-22, Bahamas

1013, 2018-04-23, Bahrain

1014, 2018-04-24, Bangladesh

1015, 2018-04-25, Barbados

1016, 2018-04-26, Belarus

1017, 2018-04-27, Belgium

1018, 2018-04-28, Belize

1019, 2018-04-29, Benin

1020, 2018-04-30, Bhutan

1021, 2018-05-01, Bolivia

1022, 2018-05-02, BosniaAndHerzegovina

1023, 2018-05-03, Botswana

1024, 2018-05-04, Brazil

1025, 2018-05-05, Brunei

1026, 2018-05-06, Bulgaria

1027, 2018-05-07, Burkina_Faso

]

;

LET vMax_Date = FirstSortedValue('ExampleDate','-ExampleDate'); // should be 5/7/2018

LET vMin_Date = FirstSortedValue(ExampleDate,ExampleDate); // should be 4/12/2018

LET vMin_Date = '=FirstSortedValue(ExampleDate,ExampleDate)';

Re: Set variable using FirstSortedValue in load script

sunny_talwar — Thu, 12 Apr 2018 17:29:54 GMT

Why not use Min and Max here?

Re: Set variable using FirstSortedValue in load script

wdchristensen — Thu, 12 Apr 2018 17:32:47 GMT

I tried that too:

LET vMax_Date = Max(ExampleDate);

For some reason I get Null.

Re: Set variable using FirstSortedValue in load script

wdchristensen — Thu, 12 Apr 2018 17:34:10 GMT

LET vMin_Date = Peek('ExampleDate',1,ExampleDate);

LET vMax_Date = Peek('ExampleDate',-1,ExampleDate);

I did get Peek to work but the data won't always be sorted as it is in my example.

Re: Set variable using FirstSortedValue in load script

sunny_talwar — Thu, 12 Apr 2018 17:34:27 GMT

Is the environmental variable set correctly?

SET DateFormat='YYYY-MM-DD';

Re: Set variable using FirstSortedValue in load script

wdchristensen — Thu, 12 Apr 2018 17:36:22 GMT

Nope!

SET DateFormat='M/D/YYYY';

Re: Set variable using FirstSortedValue in load script

sunny_talwar — Thu, 12 Apr 2018 17:36:44 GMT

You want the value in the script? Try this

Example:

LOAD * INLINE [

ExampleKey, ExampleDate, ExampleCountry

1000, 2018-04-12, Afghanistan

1001, 2018-04-11, Albania

1002, 2018-04-12, Algeria

1003, 2018-04-13, Andorra

1004, 2018-04-14, Angola

1005, 2018-04-15, AntiguaAndBarbuda

1006, 2018-04-16, Argentina

1007, 2018-04-17, Armenia

1008, 2018-04-18, Aruba

1009, 2018-04-19, Australia

1010, 2018-04-20, Austria

1011, 2018-04-21, Azerbaijan

1012, 2018-04-22, Bahamas

1013, 2018-04-23, Bahrain

1014, 2018-04-24, Bangladesh

1015, 2018-04-25, Barbados

1016, 2018-04-26, Belarus

1017, 2018-04-27, Belgium

1018, 2018-04-28, Belize

1019, 2018-04-29, Benin

1020, 2018-04-30, Bhutan

1021, 2018-05-01, Bolivia

1022, 2018-05-02, BosniaAndHerzegovina

1023, 2018-05-03, Botswana

1024, 2018-05-04, Brazil

1025, 2018-05-05, Brunei

1026, 2018-05-06, Bulgaria

1027, 2018-05-07, Burkina_Faso

];

MinMax:

LOAD Min(ExampleDate) as Min,

Max(ExampleDate) as Max

Resident Example;

LET vMin = Peek('Min');

LET vMax = Peek('Max');

Re: Set variable using FirstSortedValue in load script

wdchristensen — Thu, 12 Apr 2018 17:44:02 GMT

Thanks Sunny! Your solution definitely solves the functional purpose of getting the Min and Max date. Any idea why the FirstSortedValue isn't working?

Re: Set variable using FirstSortedValue in load script

sunny_talwar — Thu, 12 Apr 2018 17:50:10 GMT

I think these functions (Min, Max, FirstSortedValue, Sum, Avg) can't be directly used in the script to get a value... they need to be used in a load and then stored into a variable using a Peek() function... I think FirstSortedValue() will also work... (may be it might needs an additional DISTINCT... but it should work)

Re: Set variable using FirstSortedValue in load script

Qrishna — Thu, 12 Apr 2018 17:50:25 GMT

FirstSortedValue returns the 1st occurance of a value in a field based the sort weight where as min and max returns the minimum value and max value in the field.

a rough illustration:

value ,date

2,01/01

1,01/01

3,01,01

firstsortedvalue(value,value ) returns 2

min(value) =1

max(value) =3

hope that helps

Re: Set variable using FirstSortedValue in load script

sunny_talwar — Thu, 12 Apr 2018 17:58:07 GMT

This worked

Example:

LOAD * INLINE [

ExampleKey, ExampleDate, ExampleCountry

1000, 2018-04-12, Afghanistan

1001, 2018-04-11, Albania

1002, 2018-04-12, Algeria

1003, 2018-04-13, Andorra

1004, 2018-04-14, Angola

1005, 2018-04-15, AntiguaAndBarbuda

1006, 2018-04-16, Argentina

1007, 2018-04-17, Armenia

1008, 2018-04-18, Aruba

1009, 2018-04-19, Australia

1010, 2018-04-20, Austria

1011, 2018-04-21, Azerbaijan

1012, 2018-04-22, Bahamas

1013, 2018-04-23, Bahrain

1014, 2018-04-24, Bangladesh

1015, 2018-04-25, Barbados

1016, 2018-04-26, Belarus

1017, 2018-04-27, Belgium

1018, 2018-04-28, Belize

1019, 2018-04-29, Benin

1020, 2018-04-30, Bhutan

1021, 2018-05-01, Bolivia

1022, 2018-05-02, BosniaAndHerzegovina

1023, 2018-05-03, Botswana

1024, 2018-05-04, Brazil

1025, 2018-05-05, Brunei

1026, 2018-05-06, Bulgaria

1027, 2018-05-07, Burkina_Faso

];

MinMax:

LOAD FirstSortedValue(ExampleDate, ExampleDate) as Min,

FirstSortedValue(ExampleDate, -ExampleDate) as Max

Resident Example;

LET vMin = Peek('Min');

LET vMax = Peek('Max');

Re: Set variable using FirstSortedValue in load script

wdchristensen — Fri, 13 Apr 2018 20:04:30 GMT

The code below is an alternative way to get the min and the max date using the peek function. I attempted to write the code with a preceding load but I didn't get it to work...

Example:

LOAD *, 1 as ExampleCounter INLINE [

ExampleKey, ExampleDate, ExampleCountry

1018, 2018-04-28, Belize

1019, 2018-04-29, Benin

1020, 2018-04-30, Bhutan

1021, 2010-05-01, Bolivia

1022, 2020-05-02, BosniaAndHerzegovina

1023, 2018-05-03, Botswana

1024, 2018-05-04, Brazil

1025, 2018-05-05, Brunei

1026, 2018-05-06, Bulgaria

1027, 2018-05-07, Burkina_Faso

];

Example01:

NoConcatenate

Load * Resident Example

Order by ExampleDate asc;

/* Force the order so first record is the min date and the last record is the max date */

Drop Table Example;

LET vMin_Date = Peek('ExampleDate',0,Example01);

LET vMax_Date = Peek('ExampleDate',-1,Example01);

Exit Script;

Re: Set variable using FirstSortedValue in load script

wdchristensen — Fri, 13 Apr 2018 20:48:11 GMT

Cannot use an order by in a preceding load.

Order By within Preceding Load

Re: Set variable using FirstSortedValue in load script

sunny_talwar — Sun, 15 Apr 2018 12:22:56 GMT

Here is another way to find min/max using FieldValue() and FieldValueCount() function

“Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook