Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm having a table looking like below:
Serviceorders:
LOAD Serviceorders.Serviceordernr
,[Serviceorders.Service Art Serial]
, ...
RESIDENT Serviceorders
I would like to add a number (numbering up!) to this table, which is a counter per [Serviceorders.Service Art Serial].
e.g.
Serviceordernr | Service Art Serial | ... | NUMBER |
---|---|---|---|
SO_01 | 123-456-789 | 1 | |
SO_02 | 123-456-789 | 2 | |
SO_03 | 234-567-890 | 1 | |
SO_04 | 123-456-789 | 3 | |
SO_05 | 234-567-890 | 2 |
I did try something with a while loop during the load, but this was not turning in the result I was looking for.
Someone got idea's how to solve this?
thx in advance!!
Anita
Hello Anita,
Use the Previous() function to check the serial number, and the Peek() to accumulate in the script:
TableTemp:
LOAD * INLINE [
Service, Serial
SO_01, 123-456-789
SO_02, 123-456-789
SO_03, 234-567-890
SO_04, 123-456-789
SO_05, 234-567-890
];
Table:
LOAD *,
If(Serial = Previous(Serial), RangeSum(Peek('OrderNo'), 1), 1) AS OrderNo
RESIDENT TableTemp
ORDER BY Serial ASC;
DROP TABLE TableTemp;
Since the table is sorted by serial, the OrderNo will add 1 each time the current record Serial value equals to the previous record Serial value.
Hope that helps.
BI Consultant
Anita
I think this should do the trick:
Left Join (Serviceorders)
LOAD
[Serviceorders.Service Art Serial],
Serviceorders.Serviceordernr,
If ([Serviceorders.Service Art Serial] = Previous([Serviceorders.Service Art Serial]), Peek('Number') + 1, 1) AS Number
Resident Serviceorders
ORDER BY [Serviceorders.Service Art Serial], Serviceorders.Serviceordernr
(Assumes the Serviceordernr and [Service Art Serial] value combinations are unique).
Hope that helps
Jonathan
Hello Anita,
Use the Previous() function to check the serial number, and the Peek() to accumulate in the script:
TableTemp:
LOAD * INLINE [
Service, Serial
SO_01, 123-456-789
SO_02, 123-456-789
SO_03, 234-567-890
SO_04, 123-456-789
SO_05, 234-567-890
];
Table:
LOAD *,
If(Serial = Previous(Serial), RangeSum(Peek('OrderNo'), 1), 1) AS OrderNo
RESIDENT TableTemp
ORDER BY Serial ASC;
DROP TABLE TableTemp;
Since the table is sorted by serial, the OrderNo will add 1 each time the current record Serial value equals to the previous record Serial value.
Hope that helps.
BI Consultant
thx you guys!
I already tried out something with previous(), but didn't know really the functionaltiy combined with peek() ...
Realy helped me out! thx thx thx!