Pull the 2 records from th file.

[apthansh]

I have the attached file and I just want to pull in 2 records that are earliest commencement date as well as the latest commencement date.How can I put it in the where clause ?

Thanks much.

[Anil_Babu_Samineni]

You mean to say these 2 dates? 1-Oct-12 and 1-Aug-15 ??

[apthansh]

ya....that is in this scenario.but my original file has multiple records.Cannot manually write the date in where clause.

It has to be min(Date) and Max(Date) for each code.

[Anil_Babu_Samineni]

May be this?

Sample:

LOAD Code, Name, Type, Status, Commencement, Expiry

FROM Path;

Temp:

LOAD

     Min(Commencement) as MinDate,

     Max(Commencement) as MaxDate

Resident Sample;

LET vMinDate = Date(floor(peek('MinDate')));

LET vMaxDate = Date(floor(peek('MaxDate')));

Final:

NoConcatenate

LOAD * Resident Sample Where Commencement = '$(vMinDate)' or Commencement = '$(vMaxDate)';

DROP Table Sample;

[el_aprendiz111]

Hi Ansh:

MaxMin:
LOAD
Min(Commencement) AS MinDate,
Max(Commencement) AS MaxDate
FROM
[..\..\..\Documents\Downloads\Datetest.xlsx]
(ooxml, embedded labels, table is Sheet1);

LET vMinCommencement = Peek('MinDate');
LET vMaxCommencement = Peek('MaxDate');

Directory;
LOAD Code,
Name,
Type,
Status,
Commencement,
Expiry
FROM
[..\..\..\Documents\Downloads\Datetest.xlsx]
(ooxml, embedded labels, table is Sheet1)

Where WildMatch(Commencement, '$(vMinCommencement)' ,'$(vMaxCommencement)')

;

DROP Table MaxMin;

EXIT Script;

[antoniotiman]

Hi Ansh,

Temp:
LOAD Code,
Name,
Type,
Status,
Commencement,
Expiry
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1304805-286853/Datetest.xlsx"
(ooxml, embedded labels, table is Sheet1);
Join LOAD Code,MinString(Commencement) as MinC,MaxString(Commencement) as MaxC
Resident Temp
Group By Code;
NoConcatenate
LOAD Code,
Name,
Type,
Status,
Commencement,
Expiry
Resident Temp Where Commencement=MinC or Commencement=MaxC;
Drop Table Temp;

Regards,

Antonio.

[Chanty4u]

nice

[oscar_ortiz]

You may try something like this:

T1:

LOAD Distinct

Commencement as OriginalDate

FROM

"https://community.qlik.com/servlet/JiveServlet/download/1304805-286853/Datetest.xlsx"

(ooxml, embedded labels, table is Sheet1)

;

//     I would use the field value approach, especially if you have a very large table

//     Borrowed this from rwunderlich‌

T2:

LOAD

min(FieldValue('OriginalDate', recno())) as mindate,

max(FieldValue('OriginalDate', recno())) as maxdate

AUTOGENERATE FieldValueCount('OriginalDate')

;

T3:

LOAD

Code,

Name,

Type,

Status,

Commencement,

Expiry

FROM

"https://community.qlik.com/servlet/JiveServlet/download/1304805-286853/Datetest.xlsx"

(ooxml, embedded labels, table is Sheet1)

Where

Exists( mindate, Commencement )

or

Exists( maxdate, Commencement )

;

Drop Table T2;

Good luck

Oscar