Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
You could try it with firstsortedvalue() on the order number or an order date.
- Marcus
Maybe this... ?
see this
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;