topic Re: Group And Sum Discarding Un-needed data in QlikView

Group And Sum Discarding Un-needed data

dmac1971 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Group And Sum Discarding Un-needed data

sunny_talwar — Tue, 02 May 2017 15:27:50 GMT

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;

Re: Group And Sum Discarding Un-needed data

dmac1971 — Tue, 02 May 2017 15:36:52 GMT

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?

Re: Group And Sum Discarding Un-needed data

sunny_talwar — Tue, 02 May 2017 15:45:04 GMT

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

Re: Group And Sum Discarding Un-needed data

dmac1971 — Tue, 02 May 2017 15:48:50 GMT

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.

Re: Group And Sum Discarding Un-needed data

dmac1971 — Tue, 02 May 2017 15:55:16 GMT

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

Re: Group And Sum Discarding Un-needed data

sunny_talwar — Tue, 02 May 2017 16:24:55 GMT

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];

FirstSortedValue(Item, -OrderLine) as Item -> Assuming the OrderLine will be highest for item being sold