Add a number to table during load

[Anonymous]

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

[jonathandienst]

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

[Miguel_Angel_Baeyens · Accepted solution]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous]

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!