How can I fetch only the latest records?

[#1]

Hi Everyone,

I have following fields in the table, with the single or multiple records for each of the ID.

I want latest record of each of the ID in a seperate table at script level, like as below:

I dont know whether it is possible or not, but any help would be greatly appreciated. Please guide me on this. Best Regards

[#2]

Hi Niks,

              It is Possible.

In script write :

max([Registration Date]) as Max_Reg_Date

following condition should use in the report(expression)

if(Registration Date= Max_Reg_Date,Name)

Regards,

Antony.

[#3]

Hi Niks,

           Please find attached Example application.

Regards,

Antony.

[#4]

Hi Antony, Thanks for your reply and it helped me a lot. Can this be possible at script level only? I mean without using expression at report level? Regards.

[#5]

Hi Niks,

            I need to check, give me some time please....

Regards,

Antony.

[SunilChauhan]

use this code instead

Max_Value:
LOAD ID,
     Name,
     [Registration Date],
     [Expiry Date],
     [License Type]
FROM
C:\Users\lenovo\Desktop\Max_Value.xls
(biff, embedded labels, table is Sheet1$);

Test:
load ID,
[Registration Date],
Name,
max([Registration Date]) as Max_Reg_Date
Resident Max_Value,[Registration Date],Name
Group By ID
;

Test1:
Load *,
1 as junk
resident Test where [Registration Date]=Max_Reg_Date;
drop tables test,Max_Value;

[#7]

Hi Sunil,

       Thanks for your help.

I tried above code but its not showing correctly. Also note in resident you have mentioned the Field names [Registration Date](Pls check table Test)

Name

Regards,

Antony.

[SunilChauhan]

what about this code

Max_Value:
LOAD ID,
     Name,
     [Registration Date],
     [Expiry Date],
     [License Type]
FROM
C:\Users\lenovo\Desktop\Max_Value.xls
(biff, embedded labels, table is Sheet1$);

Test:
load ID,
[Registration Date],
Name,
max([Registration Date]) as Max_Reg_Date
Resident Max_Value

Group By ID,[Registration Date],Name
;

Test1:
Load ID,
MAX(Max_Reg_Date) AS Max_Reg_Date

resident Test where [Registration Date]=Max_Reg_Date;

group by  ID
drop tables test,Max_Value;

[#9]

Hi Sunil,

              Please find attached Data for your reference.

[#10]

Hi Sunil,

            ID & Max_Reg_Date showing correctly without adding of Expiry date & License type.

When i add these 2 fields in table box then its not showing correctly.