How to Extract Min(Collection Date) for each id in resident Load

[#1]

ID	CollectionDate	Sample Type	Sample Code	Dignos	Other Code	Location	Rack	Box	Position	Voume
11	01-01-2013	X	200	xxx	e	AX	A1	11	100	100
11	01-02-2013	Y	201	xxx	r	AX	B1	22	265	200
11	01-03-2013	Z	202	ooo	t	AX	C1	10	143	250
22	01-04-2013	X	203	vvvv	y	AX	A1	55	233	50
22	01-05-2013	Z	204	xxx	y	AX	C1	66	266	600

HI All,

I have Sample table as above, I wants to display 1st most samples(min(collection date))  for each individual IDs as well as rest of the columns . I can do that in Sql server but struggling with qlikview, I have table loaded already as named  "Sample". any help would be very helpfull,

Thanks in advance!

[Miguel_Angel_Baeyens]

Hi,

Check the attached application as one way to do it. I do first a load of all values I really want to load with FirstSortedValue() that is an aggregation function that returns the first value in the field passed as first parameter (in my example, ID and Sample Type), according to the weigh of the numeric value passed as second parameter (in my example, the first date for each ID & Sample Type), then use it in the WHERE clause of the RESIDENT.

Hope that helps.

Miguel

[#3]

Hi,

Here's a solution using firstvalue:

Table1:
LOAD * Inline [
ID, CollectionDate
11, 01-01-2013
11, 01-02-2013
11, 01-03-2013
22, 01-04-2013
22,01-05-2013 ];

MinCollectionDate:
LOAD
ID,
FirstValue(CollectionDate) as MinCollectionDate
Resident Table1
Group by ID;

At this point you have two tables, I've check on my computer and it works. You could also join the MinCollectionDate back into Table1, it's really up to you.

Best,

Matt

[#4]

HI Matt,

I have tried the way you suggest but what do you mean by join em ? Left join or inner join or concatinate?

[#5]

HI

Thanks for your reply but Sorry got bit confused,

Can i do something like

SELECT * FROM table1 WHERE CollectionDate in (Select min(CollectionDate) from table1 group by ID)

[#6]

Hi Abu,

My solution left you with 2 tables. If you'd prefer to just have 1 table with all you data, you could join the mincollectiondate field back into your main table using a left join. The example below works:

Table1:
LOAD * Inline [
ID, CollectionDate
11, 01-01-2013
11, 01-02-2013
11, 01-03-2013
22, 01-04-2013
22,01-05-2013 ];

MinCollectionDate:
LOAD
ID,
FirstValue(CollectionDate) as MinCollectionDate
Resident Table1
Group by ID;

LEFT JOIN (Table1)
LOAD
      ID,
      MinCollectionDate
RESIDENT MinCollectionDate;

Drop Table MinCollectionDate;

Hope that helps.

Matt

[#7]

Hi Matt

Many thanks! I will try and let you know.

Regards

Abu

[#8]

Hi Matt

I have done the Left Join, but still its giving me all the results by replacing all sample date with min date of the individual ID, I wants to include all the fields from  sample table but only the first collection date of each ID.

Regards

Sample:

LOAD ID,
CollectionDate,
Participantcode,
Scode,
Diagno,
Ecode,
Freezer,
Rack,
Box,
Position,
Sampletype,
Vol,
Comments,
PHcode,
SERAPH,
Ocode
FROM
Y:\Master.QVD
(qvd);

MinCollectionDate:
LOAD
ID,
FirstValue(date(CollectionDate)) as MinDate
Resident Sample
Group by ID ;


LEFT JOIN (Sample)
LOAD ID,
MinDate
RESIDENT MinCollectionDate;

[CELAMBARASAN]

this might be

Sample:

LOAD ID,
CollectionDate,
Participantcode,
Scode,
Diagno,
Ecode,
Freezer,
Rack,
Box,
Position,
Sampletype,
Vol,
Comments,
PHcode,
SERAPH,
Ocode
FROM
Y:\Master.QVD
(qvd);

INNER JOIN
LOAD
ID,
FirstValue(date(CollectionDate)) as CollectionDate
Resident Sample
Group by ID ;

[#10 · Accepted solution]

Hi Abu,

I'm not sure what happened, could you post an example? If not, I've attached the sample I was working from.

Hope that helps.

Matt