load distinct with max date

[prees959]

Hi

I need to load a qvd with a distinct ID field and also where the field [file_received_date] is the maximum ..is this possible?

Thanks,

Phil

[shraddha_g]

Load Distinct

IDField,

max(File_Received_Date) as File_Received_Date

From .....qvd

Group by IDField;

[sunny_talwar]

I would think it is... how does your data look like? Can you share a sample?

[prees959]

Hi,

I have many fields in my dataset which will cause a large group by statement - is there a workaround?

[shraddha_g]

You can Left join this data to your original table

[jonathandienst]

>>is there a workaround?

You could load just the IDs and the max Dates and then left join the main load to filter for the maximum IDs

Data:

LOAD ID, Max(DateField) as DateField

FROM sourcefile.qvd (qvd)

GROUP By ID;

Left Join(Data)

LOAD *

FROM sourecfile.qvd;

(Where DateField is the name of the date field and sourcefile.qvw is the name of the source file).