Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

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
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Creator
Creator
Author

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Specialist
Specialist

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
Master III
Master III

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
MVP
MVP

nice

oscar_ortiz
Partner - Specialist
Partner - Specialist

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