Num Orders

[Ran]

hi 🙂

i have orders with many items

for example

OrderID  Item     

222              a

222             b

222            c

123          d

321           e

i want to make table in qlik  that will add to OrderId "-" and his num of duplicate (the first is zero so just orderId)

for example

OrderID  Item      NEWORDERID

222              a              222

222             b            222-1

222            c              222-2

123          d                 123

321           e                 321

tried to make Rowno() but i get  222-1235443(row)

and tried to use Peek but didnt work

their is and option to go with Peek 2 lines up and not just the Previous?OR  have any Idea to solve it? 

Thanks

[ogster1974]

Try this 

TmpOrders:
Load * Inline
[
OrderID,Item
222,a
222,b
222,c
123,d
321,e
];

NoConcatenate
Orders:
Load *,
OrderID & If(LineNum>0, '-'& LineNum,'') as NewOrderId
;
Load *,
If(OrderID<>Peek(OrderID),0,peek('LineNum')+1) as LineNum
Resident TmpOrders;

Drop table TmpOrders;

[vinieme12 · Accepted solution]

Just Use Autonumber() 

the data doesn't need to be sorted

TEMP:

load *,OrderID&'-'&AutoNumber(OrderID&Item,OrderID) as NewOrderID inline [
OrderID,Item
222,a
222,b
222,c
123,d
321,e
];

[Ran]

Thanks Guys😁