Set variable using FirstSortedValue in load script

[wdchristensen]

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]

Why not use Min and Max here?

[wdchristensen]

I tried that too:

LET vMax_Date = Max(ExampleDate);

For some reason I get Null.

[wdchristensen]

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.

[sunny_talwar]

Is the environmental variable set correctly?

SET DateFormat='YYYY-MM-DD';

[wdchristensen]

Nope!

SET DateFormat='M/D/YYYY';

[sunny_talwar]

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

[wdchristensen]

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

[sunny_talwar]

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)

[Qrishna]

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