topic SQL Query to get most latest record of ID's in QlikView

SQL Query to get most latest record of ID's

Wed, 15 Aug 2012 18:12:51 GMT

Hi All,

I have a table which has ID,Name,Skill1,Skill2,Skill3,Mthyr as fileds in it.I need to get the max record of a ID.Lets supppose ID =1 has Mnthyr as 201209 as the latest record and ID =2 has 201211 as the latest record.In my resultant table i want only these records i meant latest records for the Id's.

Thanks in advance.

Re: SQL Query to get most latest record of ID's

Wed, 15 Aug 2012 19:02:56 GMT

Check the attachment with qvw and your excelfile. I think that's what you're looking for.

Hope it helps.

Short explanation:

- make a query in which you group by ID on max(date)

- join the fields you miss due to the group by query

Re: SQL Query to get most latest record of ID's

manojkvrajan — Wed, 15 Aug 2012 19:18:03 GMT

Lavanya, I assume MSID and Mnthyr combination identifies the unique record (as Key). Please see if the below mentioned script and attached samples helps.

Temp:
LOAD DISTINCT MSID,
max(Mnthyr) AS Mnthyr
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1)
GROUP BY MSID;

TempKey:

LOAD *,
MSID&Mnthyr AS %Key
RESIDENT
Temp;

Book:
LOAD MSID&Mnthyr AS %Key,
*
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE EXISTS (%Key,MSID&Mnthyr);

DROP TABLES TempKey,Temp;
DROP FIELD %Key;

Re: SQL Query to get most latest record of ID's

Thu, 16 Aug 2012 07:30:51 GMT

Hi Manoj,

Thanks for the reply I just added the filed Mnthyr but I need to get the Latest record of all th eMSId because of the skill info is not changed the same record will be maintained for a particular MSID.In that case i need to look at the latest record of all the MSID's irrespective of

Please let me know if you need more info.

Re: SQL Query to get most latest record of ID's

Thu, 16 Aug 2012 07:31:13 GMT

Hi Manoj,

Please let me know if you need more info.

Re: SQL Query to get most latest record of ID's

daveamz — Thu, 16 Aug 2012 07:47:07 GMT

Hi Lavanya,

See if this code will do what you need:

maxOfID:
load max(Mnthyr) as MAXID, MSID
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1) Group by MSID;
data:
load MSID&'_'&MAXID as key Resident maxOfID;
left join (data)
LOAD
*,
MSID&'_'&Mnthyr as key
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
drop Table maxOfID;

Regards,

David

Re: SQL Query to get most latest record of ID's

manojkvrajan — Thu, 16 Aug 2012 13:24:45 GMT

Lavanya, My understanding of the requirement is to display the skills of the MSID based on the latest Monthyear and ignore the rest. Please find below the quick snapshot. Please provide more information about the additional requirements on this sample data with an example