Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Lets say I have a data file that contains multiple lines for each sales order. I want to take the line with the highest value, keep that line, but have its total value a sum of all the lines value. Then when this is figured out, dispose of all the lines leaving me with one line?
I've a simple excel file attached showing what I am trying to achieve, and a simple inline QV loaded file.
At the end I only need a data file with OrderNo, OrdDate, Most Expensive Item, and Total Sell from order. Hopefully I have explained myself well enough to get across what I am trying to achieve?
Try this
Table:
LOAD * INLINE [
Ord No, Ord Date, Item, Desc, Sell
50001, 01/01/2017, Car 1, Car, 10500
50001, 01/01/2017, Option 1.1, Metalic Paint, 1500
50001, 01/01/2017, Option 1.2, Sunroof, 1200
50001, 01/01/2017, Option 1.3, Audio System, 875
50001, 01/01/2017, Option 1.4, Run Flat Tyres, 200
50002, 05/01/2017, Car 2, Car, 10500
50002, 05/01/2017, Option 2.1, Metalic Paint, 1500
50002, 05/01/2017, Option 2.2, Sunroof, 1200
50002, 05/01/2017, Option 2.3, Audio System, 875
50002, 05/01/2017, Option 2.4, Run Flat Tyres, 200
50002, 05/01/2017, Option 2.5, Sports Trim, 400
];
FinalTable:
LOAD [Ord No],
[Ord Date],
FirstValue(Item) as Item,
Sum(Sell) as Sell
Resident Table
Group By [Ord No], [Ord Date];
DROP Table Table;
Sunny, thanks for fast reply, much appreciated. The data will need to be sorted by value first of all, the example file already has this done though, but real file hasn't?
So how will you know which Item to pick? Is there some logic behind picking Car? Or will it always start with Car?
Each order has unique Order No, each order no is made up of 5 to 10 lines. One line is the item being sold, this will always be the highest value line. Ideal output at the end will be one line of data for each order, with the Order No, the highest value item from the initial data load, and the total value of all of the lines.
Assume I load the data and order by the orderno, and then the orderline value. Then reload as you have shown in example above?
You can try that or you can do this
FinalTable:
LOAD [Ord No],
[Ord Date],
FirstSortedValue(Item, OrderLine) as Item, -> Assuming the OrderLine will be smallest for item being sold
Sum(Sell) as Sell
Resident Table
Group By [Ord No], [Ord Date];
or
FirstSortedValue(Item, -OrderLine) as Item -> Assuming the OrderLine will be highest for item being sold