Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

dateformat for peek

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

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

2 Replies
SunilChauhan
Champion II
Champion II

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

it will consider only Transdatum first and last date

Sunil Chauhan
hic
Former Employee
Former Employee

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