topic Re: How to Extract Min(Collection Date) for each id in resident Load in QlikView

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

Wed, 28 Aug 2013 19:09:48 GMT

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!

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

Miguel_Angel_Baeyens — Wed, 28 Aug 2013 19:32:17 GMT

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

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

Wed, 28 Aug 2013 19:34:27 GMT

Hi,

Here's a solution using firstvalue:

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

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

Thu, 29 Aug 2013 21:22:03 GMT

HI Matt,

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

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

Thu, 29 Aug 2013 21:29:11 GMT

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)

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

Thu, 29 Aug 2013 21:33:25 GMT

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

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

Thu, 29 Aug 2013 21:35:35 GMT

Hi Matt

Many thanks! I will try and let you know.

Regards

Abu

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

Fri, 30 Aug 2013 09:38:38 GMT

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;

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

CELAMBARASAN — Fri, 30 Aug 2013 09:41:37 GMT

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 ;

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

Sat, 07 Sep 2013 15:24:08 GMT

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