Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)';
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');
Why not use Min and Max here?
I tried that too:
LET vMax_Date = Max(ExampleDate);
For some reason I get Null.
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.
Is the environmental variable set correctly?
SET DateFormat='YYYY-MM-DD';
Nope!
SET DateFormat='M/D/YYYY';
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');
Thanks Sunny! Your solution definitely solves the functional purpose of getting the Min and Max date. Any idea why the FirstSortedValue isn't working?
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)
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