Unable to find min value of a date using the "Fastest method"

[pravinboniface]

Hello all!  I am trying to find the minimum date using the "fastest method"  However, either the date is not getting formatted correctly or my code does not work.  I am expecting the trace statement to print 2024-01-01.

I understand there are others ways, but I'm reading the dates from a 500M row QVD and I need to follow this method.  Sample code is below.  Thanks always for the support.

mydates:
load * inline [
start_date
2024-02-01
2024-01-01
];

min_date:
NoConcatenate

// **** Unable to get the minimum value
load min(date(date#(start_date,'YYYY-MM-DD'),'YYYY-MM-DD')) as start_date;
// **** Unable to get the minimum value

load FieldValue('start_date',RowNo()) as start_date
AutoGenerate(FieldValueCount('start_date'));

let vStart_Date = peek('start_date',0);

trace >>> $(vStart_Date);

drop table mydates;
exit Script;

[Aditya_Chitale]

Can you share sample data containing few rows ? You can exclude unnecessary columns. I just need the date column

Regards,

Aditya

[Qrishna]

Try Below:

mydates:
Load *,
Num(Date#(start_date, 'YYYY-MM-DD')) as DateKey
;
load * inline [
start_date
2024-02-01
2024-01-01
];

MinMax:
LOAD
Min(DateKey) as MinDate,
Max(DateKey) as MaxDate
RESIDENT mydates;

let vMinDate = num(peek('MinDate', 0, 'MinMax'));
let vMaxDate = num(peek('MaxDate', 0, 'MinMax'));

Calendar:
Load
Num(Date(DateNum, 'DD-MMM-YYYY')) as DateKey,
Date#(Date(DateNum), 'DD-MMM-YYYY') as Date,
Month(DateNum) as Month,
Year(DateNum) as Year,
Month(DateNum) & '-' & Year(DateNum) as MonthYear;
Load distinct
$(vMinDate) + IterNo() - 1 as DateNum
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

[pravinboniface]

Hi Aditya, The code I provided has sample data.

[pravinboniface]

Thanks, Qrishna.  I am able to get min(date) through other methods, but I wanted to use the method in my sample code as I will be reading a QVD with 500M rows.

[Kushal_Chawda]

@pravinboniface  set the date format variable in main tab according to the format of start_date

SET DateFormat='YYYY-MM-DD';

Now below should work

mydates:
load * inline [
start_date
2024-02-01
2024-01-01
];

min_date:
load date(min(FieldValue('start_date',RowNo())),'YYYY-MM-DD') as min_date
AutoGenerate FieldValueCount('start_date');

let vStart_Date = peek('min_date');

trace >>> $(vStart_Date);

drop table mydates;

[pravinboniface]

Thanks @Kushal_Chawda but still not working for me.  This is what I get when I run your code:

mydates << 89d22fab-3a2e-44db-8b21-f6d45223c021 Lines fetched: 2 min_date << AUTOGENERATE(2) Lines fetched: 1 >>>

Creating search index Search index creation completed successfully

App saved

Finished successfully

0 forced error(s)

0 synthetic key(s)

[Kushal_Chawda · Accepted solution]

@pravinboniface  Replace rowno() with recno(). Inter-record functions always refer to recno()

mydates:
load * inline [
start_date
2024-02-01
2024-01-01
];

min_date:
load date(min(FieldValue('start_date',RecNo())),'YYYY-MM-DD') as min_date
AutoGenerate FieldValueCount('start_date');

let vStart_Date = peek('min_date');

trace >>> $(vStart_Date);

drop table mydates;

[Muthukumar_77]

Hi,

Try below script,

mydates_1:
load * inline [
start_date
2024-02-01
2024-01-01
];

NoConcatenate
mydates_2:
Load Num(Date#(Trim(start_date),'YYYY-MM-DD')) as start_date_num Resident mydates_1;

Drop Table mydates_1;

NoConcatenate
mydates_3:
Load Min(start_date_num) as Min_start_date_num Resident mydates_2;

Drop Table mydates_2;

let vStart_Date = Date(peek('Min_start_date_num',0,'mydates_3'),'YYYY-MM-DD');

Drop Table mydates_3;

[pravinboniface]

Thank you @Kushal_Chawda Much appreciated.