Removing duplicates, getting latest value via multiple criteria

[#1]

Hi there,

I have a set of data like this

Product No	Date Created	Record No	Date Altered	Reason
1	4/8/2014	RN0001	4/6/2014	Damaged
1	4/8/2014	RN0025	4/6/2014	AutoRepaired
1	4/1/2015	RN9090	4/11/2014	Damaged
2	5/9/2015	RN0002	3/9/2015	Creation
3	6/8/2015	RN0003	6/7/2015	Damaged
3	6/8/2015	RN0004	6/7/2015	AutoRepaired
3	10/10/2015	RN0006	9/9/2015	Damaged

I would like to remove duplicated based on product no, and get the latest value. Which should look like this

(This is to be done in load script)

Product No	Date Created	Record No	Date Altered	Reason
1	4/1/2015	RN9090	4/11/2014	Damaged
2	5/9/2015	RN0002	3/9/2015	Creation
3	10/10/2015	RN0006	9/9/2015	Damaged

Basically for each product no, i would like to obtain the data that has the LATEST DateCreated,RecordNo,DateAltered.

Thank you.

[#2]

Hi sunnyT,

thank you for your prompt reply.

I found the problem.

My date created field is recognized as a string which i am not sure how sort is working it out.

My solution was during load,

Date#(datecreated,'DD/MM/YYYY') as datecreated

then it solves the issue.

One small question,

for order by asc or desc, do they sort by numerical or A to Z? or is it possible to specify?

because in this case, my datecreated field is not recognized as a date, thats why i have this problem.

Thank you.

[sunny_talwar]

Not sure I understand your question, but numericals and strings can both be sorted

Asc

Numerical -> 1, 2, 3, 4....

String ->  A, B, C, D

Desc

Numerica -> 4,3,2,1...

String -> Z, Y, X, ....

was that your question?

[#4]

So basically, if i dont do Date#(datecreated,'DD/MM/YYYY') as datecreated when loading the table, my datecreated will not be sorted according to my order by. Only after doing that, i can sort it according to what i want.

If say a field, is not specified to be a date field or string, and i put say 21/11/2015, it will align to left side of the field (assuming you open a tablebox or straight table, regardless.)

And it will not sort correctly.

Only after doing Date#(datecreated,'DD/MM/YYYY') as datecreated, then would it sort correctly.

Why is this so?