Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
apthansh
Contributor

Pull the 2 records from th file.

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.

7 Replies

Re: Pull the 2 records from th file.

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
apthansh
Contributor

Re: Pull the 2 records from th file.

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.

Re: Pull the 2 records from th file.

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
el_aprendiz111
Valued Contributor

Re: Pull the 2 records from th file.

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
Honored Contributor III

Re: Pull the 2 records from th file.

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
Esteemed Contributor III

Re: Pull the 2 records from th file.

nice

Partner
Partner

Re: Pull the 2 records from th file.

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;

267248.png

Good luck

Oscar