Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Set variable using FirstSortedValue in load script

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)';

Tags (3)
1 Solution

Accepted Solutions
Highlighted

Re: Set variable using FirstSortedValue in load script

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');

View solution in original post

13 Replies
Highlighted

Re: Set variable using FirstSortedValue in load script

Why not use Min and Max here?

Highlighted
Specialist
Specialist

Re: Set variable using FirstSortedValue in load script

I tried that too:

LET vMax_Date = Max(ExampleDate);

For some reason I get Null.

Highlighted
Specialist
Specialist

Re: Set variable using FirstSortedValue in load script

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.

Highlighted

Re: Set variable using FirstSortedValue in load script

Is the environmental variable set correctly?

SET DateFormat='YYYY-MM-DD';

Highlighted
Specialist
Specialist

Re: Set variable using FirstSortedValue in load script

Nope!

SET DateFormat='M/D/YYYY';

Highlighted

Re: Set variable using FirstSortedValue in load script

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');

Highlighted
Specialist
Specialist

Re: Set variable using FirstSortedValue in load script

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

Highlighted

Re: Set variable using FirstSortedValue in load script

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)

Highlighted
Specialist III
Specialist III

Re: Set variable using FirstSortedValue in load script

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