topic Re: Add a number to table during load in QlikView

Add a number to table during load

Anonymous — Thu, 20 Oct 2011 07:40:27 GMT

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

Re: Add a number to table during load

jonathandienst — Thu, 20 Oct 2011 08:37:54 GMT

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

Re: Add a number to table during load

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 08:38:56 GMT

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

Re: Add a number to table during load

Anonymous — Thu, 20 Oct 2011 08:56:44 GMT

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!