issue with aggregation

[ashfaq_haseeb]

Dear All

I have a table with matchtype, short item number, quantity received, DateReceived

and when I load data

LOAD

[Match Type],

[Short Item Number]

,

[Quantity Received],

[Date Received]

FROM

<path>\PurchaseOrderLastReceived.qvd

(

qvd) where [Short Item Number]='93285' and [Match Type] ='1';

the output is like this

Match Type Short Item Number quantity received date Received

1 93285 1000 100184

1 93285 18000 100185

1 93285 370 100026

1 93285 37745 101182

now what i want is for max date in table i should load that record only i.e here max date is 101182 so I want to fetch that particular record only not all.

How wouls i do this?

when i do group by see below.

LOAD

[Match Type],

[Short Item Number]

,

sum([Quantity Received]) as [Quantity Received],

max([Date Received]) as [Date Received],

FROM

C:\Users\user1\Desktop\Developement\Centralized\Daily\Ashfaq\PurchaseOrderLastReceived.qvd

(

group by [Match Type],[Short Item Number];

qvd) where [Short Item Number]='93285' and [Match Type] ='1';

it add up all the quantity.

but i need to load only 1 record with max date.

How would i do that.

Regards

Ashfaq

[ashfaq_haseeb]

can any one help me on this issue.

my main concern is to load records from table for only max date

Hope someone can help me out in this issue.

[tresesco]

Hi,

Try the following...

Sort(Order by) the data on date field and load it. Then use the PEEK() function to get it even into a variable using the RESIDENT of the prevously loaded table.

[ashfaq_haseeb]

hi tresesco

thanks for your reply. This is not whta i'm looking for

i want to load only those fields with max date at sql level itself.

how can i do that any ideas?

[#5 · Accepted solution]

Hi,

see if this example is helpful for you.

The idea is load actual records first and than do the aggregation.

Good luck!

Rainer

[ashfaq_haseeb]

Hi Rainer,

Thanks for the reply man.

I followed the script provided by you.

but its not working for me. I have attached a sample here.

but in output i should get 0ne record like you but i'm getting 5 records can u tell me what's wrong in it.

I should get only 1 recrd in table like this.

1, 932855, 37745, 101182

I really appreciate your concern man.

Thanks and Regards

Ashfaq

[#7]

Load

Match_type as New_Match,
SHTINO as New_SHTINO,
Qty as New_Qty,
Date as New_Date
Resident Temp_Customer
Where Match_type <> Previous(Match_type) //find the actual records
Order By Qty, Date DESC; //Sort the table

You are using qty to check with previous value .it is having different values ,thats why its loading all the data. if u use Match_type , it will only load one data or if u r using SHTINO , it will load only two values. In Match_type, all the values are same and In SHTINO,only two different values. Hope this will help u...

thanks