How to display the latest record only of specific field based on date and time

[Mhidz]

I need to display the latest record only of specific SN based on date and time from transformed QVD. Table should return one row per SN.

https://drive.google.com/file/d/12nPTf9t7xoT7SSLtVg3Npx7GBQ3ANoAz/view?usp=sharing

Load

SN,

PI_Machine,

PI_Result,

PI_Date,

PI_time

From Raw_QVD

[shiveshsingh]

Hi

You can calculate the max datetime and then group by it with all the fields in the script.

[StarinieriG]

Hi,

you could try with

Load

SN,

FirstValue(PI_Machine) as PI_Machine,
FirstValue(PI_Result) as PI_Result,
FirstValue(PI_Date) as PI_Date,
FirstValue(PI_time) as PI_time
From Raw_QVD (qvd)
Group By
SN
order by
PI_Date desc,
PI_time desc
;

[Mhidz]

Hi,

I encountered error on Order by. See below link.

https://drive.google.com/file/d/1gUO-U7V5_cGeNv50-A6KxRAIc39W3zSl/view?usp=sharing

Thanks,

Mhidz

[StarinieriG]

Hi,

sorry I forgot an important detail:

you could use order by only if you had loaded the table before.

So, you could load it and than, use the same script using resident

[Mhidz]

Hi,

I'm loading my script using raw qvd file only not from database.