Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
@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;
Can you share sample data containing few rows ? You can exclude unnecessary columns. I just need the date column
Regards,
Aditya
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);
Hi Aditya, The code I provided has sample data.
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.
@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;
Thanks @Kushal_Chawda but still not working for me. This is what I get when I run your code:
@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;
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;
Thank you @Kushal_Chawda Much appreciated.