topic issue with aggregation in QlikView

issue with aggregation

ashfaq_haseeb — Wed, 07 Jul 2010 09:24:09 GMT

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

issue with aggregation

ashfaq_haseeb — Wed, 07 Jul 2010 11:53:30 GMT

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.

issue with aggregation

tresB — Wed, 07 Jul 2010 12:14:47 GMT

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.

issue with aggregation

ashfaq_haseeb — Wed, 07 Jul 2010 12:28:45 GMT

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?

issue with aggregation

Wed, 07 Jul 2010 12:55:57 GMT

Hi,

see if this example is helpful for you.

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

Good luck!

Rainer

issue with aggregation

ashfaq_haseeb — Wed, 07 Jul 2010 14:32:42 GMT

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

issue with aggregation

Wed, 07 Jul 2010 15:10:26 GMT

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