Script Help

[Anonymous]

Hi all,

Please help me on this scripting problem. For Example i has two table in my data

TableA:

Load ID

      Sales201507

      YearMonth

From

Sales201507.qvd(qvd);

Concatenate

Load ID

      Sales201507

      YearMonth

From

Sales201507.qvd(qvd);

After that, i need get the maximum sale in every month. How can i get the max sale in the script?Is it like this kind of way?

Final Result:

Load ID,

        Max(Sales&YearMonth) as MaxSales&YearMonth

Resident TableA

group by ID;

[Peter_Cammaert]

Yes, almost. This works better in my opinion:

[Final Result]:

Load YearMonth,

     Max(Sales) as MaxSales,

     FirstSortedValue(ID, -Sales) AS MaxSalesID

Resident TableA

group by YearMonth;;

This LOAD statement returns the maximum Sales per YearMonth, and adds the corresponding ID.

Best,

Peter

[manojkulkarni]

In Your Final Table,

Final Result:

Load ID,

        Max(Sales) as MaxSales

Resident TableA

group by ID, YearMonth;

Above load will give you aggregate table having below data

ID | YearMonth | MaxSales

If you need the data monthwise, then you should have month filed to do so. In existing data itself you can get month from you YearMonth column and follow the same.