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