Load-editor group by dependent on order

[BTLR]

Hi all, 

I have the following raw data:

Category        |    Qty

A                         |   10

A                         |  15

A                        |   10

B                        |  50

B                        |  100

A                       |  10

And I would like to load it such that it appears like this:

Category        |    Qty

A                         |   35 (= 10+15+10)

B                        |   150 (=50 +100)

A                        |  10

Does someone know how to do this? 

[JHuis]

Yes,

you have to add an Group by statement. 

https://www.google.com/search?q=group+by+qliksense&rlz=1C1GCEB_enNL944NL944&oq=group+by+qliksense&aq...

Table:

Load

Category,

sum(Qty) as Qty

Group by Category;

Load

Category,

Qty

From...

[BTLR]

Dear JHuis,

Thank you for your response. 

However this will give me the following right

Category        |    Qty

A                         |   45 (= 10+15+10+ 10)

B                        |   150 (=50 +100)

Category        |    Qty

A                         |   35 (= 10+15+10)

B                        |   150 (=50 +100)

A                        |  10

The order of the rows matters here. We have an index available by the way. 

Thanks in advance. 

instead of 

[Or · Accepted solution]

I think this approach should work - number the groups based on peeking, and then group by that field. Keep in mind that if you eliminate Field3 from the load, which I did to get your exact target data, you won't be able to pick out the different groups later, so I'd suggest leaving it intact.

Source:
Load Field1, Field2, If(Field1 <> Peek(Field1),RecNo(),Peek(Field3)) as Field3;
Load * INLINE [
Field1, Field2
A, 10
A, 15
A, 10
B, 50
B, 100
A, 10
];

Load Field1, Sum(Field2) as Qty
Resident Source
GROUP BY Field1, Field3;

Drop Table Source;