Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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
HI Matt,
I have tried the way you suggest but what do you mean by join em ? Left join or inner join or concatinate?
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)
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
Hi Matt
Many thanks! I will try and let you know.
Regards
Abu
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;
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 ;
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