topic load distinct with max date in QlikView

load distinct with max date

prees959 — Fri, 22 Sep 2017 11:16:07 GMT

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 — Fri, 22 Sep 2017 11:22:06 GMT

Load Distinct

IDField,

max(File_Received_Date) as File_Received_Date

From .....qvd

Group by IDField;

sunny_talwar — Fri, 22 Sep 2017 11:22:41 GMT

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

prees959 — Fri, 22 Sep 2017 11:29:41 GMT

Hi,

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

shraddha_g — Fri, 22 Sep 2017 11:34:43 GMT

You can Left join this data to your original table

jonathandienst — Fri, 22 Sep 2017 11:54:32 GMT

>>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).