Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dmac1971
Creator III
Creator III

Group And Sum Discarding Un-needed data

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?

6 Replies
sunny_talwar

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;

dmac1971
Creator III
Creator III
Author

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?

sunny_talwar

So how will you know which Item to pick? Is there some logic behind picking Car? Or will it always start with Car?

dmac1971
Creator III
Creator III
Author

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.

dmac1971
Creator III
Creator III
Author

Assume I load the data and order by the orderno, and then the orderline value.  Then reload as you have shown in example above?

sunny_talwar

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