dateformat for peek

[#1]

Hi!

I'm loading transaction date from a column in table 1 and from filename in table 2.

Then I'm concatenating just that field to create a complete set of dates. From that I pick min and max dates so that I can create a master calendar.

If I look in the table temp_date all dates are there but EndDate only concider dates in table 1.

File name is blabla_200911.xls, one per month that is. _200912, _201001

Assuming it's a format issue? Using QV 10

table_1:
date(date#(somefield,'YYMMDD'),'YYYY-MM-DD') as TransDatum
table_2:
date(date#(subfield(filebasename(), '_', 3) & num(1, 00),'YYYYMMDD'),'YYYY-MM-DD') as TransDatum,
// Create a temp table with all TransDatum
temp_date:
LOAD
TransDatum
Resident table_1;
Concatenate(temp_date) load
TransDatum
Resident table_2;
time:
Let StartDate = num(peek('TransDatum',0,'temp_date'));
Let EndDate = num(peek('TransDatum',-1,'temp_date'));

[SunilChauhan]

peek is used to find first and last record using 0 and -1

it will consider only Transdatum first and last date

[hic · Accepted solution]

I can see several potential problems:
First, I suspect the third parameter of Subfield should be 2 not 3.
Secondly, the second parameter of the Num function should have single quotes.
Thirdly, I think you need to use the Min and Max functions.

Try this after the concatenation:

MinMaxDate:
Load
Min(temp_date) as MinDate,
Max(temp_date) as MaxDate
resident temp_date;

Let StartDate = num(peek('MinDate',-1,'MinMaxDate'));
Let EndDate = num(peek('MaxDate',-1,'MinMaxDate'));