Solved: Re: Set variable using FirstSortedValue in load sc... - Page 2 - Qlik Community

wdchristensen · ‎2018-04-12

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

sunny_talwar · ‎2018-04-12

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

wdchristensen · ‎2018-04-13

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;

wdchristensen · ‎2018-04-13

Cannot use an order by in a preceding load.

Order By within Preceding Load

sunny_talwar · ‎2018-04-15

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

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

Set variable using FirstSortedValue in load script