Average order amount of all first orders

[qlikconsultant]

Hi,

I have an table of all orders like that:

Customer	Ordernumber	Amount
1	1	10,00
1	2	15
2	3	12,50
1	4	13
3	5	20
2	6	23
3	7	11
1	8	10

and i need only the amount of the first orders.

Thanks for any helps.

[#2]

Hi Markus,

Can you please try using the below script.

input:

load * inline [

Customer,    Ordernumber,    Amount

1,    1    , "10,00"

1,    2,    "15"

2,    3,    "12,50"

1,    4,    "13"

3,    5,    "20"

2,    6,    "23"

3,    7,    "11"

1,    8,    "10"

];

NoConcatenate

output:

load Customer,

     Ordernumber,

     Amount,

     if(Customer<>Previous(Customer),1,Peek(rank)+1) as rank

Resident input Order by Customer, Ordernumber;

NoConcatenate

finaloutput:

load Customer as customer,

     Ordernumber as ordernumber,

     Amount as amount

Resident output where rank=1;    

Thanks,
Sreeman

[marcus_sommer]

You could try it with firstsortedvalue() on the order number or an order date.

- Marcus

[#4]

Maybe this... ?

[Kushal_Chawda]

see this

[sunny_talwar]

Couple of options to remove them from script:

1) Using Right Join

Table:

LOAD * Inline [

Customer,    Ordernumber,    Amount

1,    1, 10.00

1,    2,    15

2,    3,    12.50

1,    4,    13

3,    5,    20

2,    6,    23

3,    7,    11

1,    8,    10

];

Right Join (Table)

LOAD Customer,

  Min(Ordernumber) as Ordernumber

Resident Table

Group By Customer;

2) Using FirstSortedValue (as Mentioned by Marcus Above)

Table:

LOAD Customer,

  Min(Ordernumber) as Ordernumber,

  FirstSortedValue(Amount, Ordernumber) as Amount

Group By Customer;

LOAD * Inline [

Customer,    Ordernumber,    Amount

1,    1, 10.00

1,    2,    15

2,    3,    12.50

1,    4,    13

3,    5,    20

2,    6,    23

3,    7,    11

1,    8,    10

];

If you want to keep all the data but just want to show the Min ordernumber lines, then you can create flags for it

3) Flag

Table:

LOAD * Inline [

Customer,    Ordernumber,    Amount

1,    1, 10.00

1,    2,    15

2,    3,    12.50

1,    4,    13

3,    5,    20

2,    6,    23

3,    7,    11

1,    8,    10

];

Left Join (Table)

LOAD Customer,

  Min(Ordernumber) as Ordernumber,

  1 as Flag

Resident Table

Group By Customer;