Creating new field

[#1]

Hi all,

I have 3 fields:

Value	Quantity	Average Value
1000	4	250
5000	10	500
3000	3	1000
2000	20	100
1000	5	200
3000	30	100
5000	2	2500
...	...	...
...	...	...

now I want to get a 4th field that looks like the following:

Avg * Qty
250
250
250
250
500
500
...
..(10 times 500)
..
1000
1000
1000
...

Does anyone have an idea how to achieve this?

Thank you very much in advance!

Best regards

[its_anandrjs]

Hi,

Try to use Iterno() function here that may help you a lot.

Regards

Anand

[maxgro]

a:

LOAD Value,

     Quantity,

     [Average Value]

FROM

[http://community.qlik.com/thread/158239]

(html, codepage is 1252, embedded labels, table is @1)

where left(trim(Value),1)<>'.' ;

b:

load

  [Average Value],

  IterNo()

Resident

  a

While 0+IterNo() <= Quantity;

[#4]

Thank you for your quick responses!!

Is it possible to store these values in an own field?

I have now:

Avg     IterNo

x          1

x          2

y          1   

y          2

y          3

and so on, which is exactly what I was looking for, but I need the new Avg values in a new field.

[maxgro]

maybe this?

b:

load

  [Average Value] as NameOfNewField,

  IterNo()

Resident

  a

While 0+IterNo() <= Quantity;

[pokassov]

b:

load

  [Average Value] as [New avg],

  IterNo()

Resident

  a

While 0+IterNo() <= Quantity;

[MK_QSL]

Use below Script..

Test:

Load

  Value,

  Quantity,

  Quantity as TempQty,

  Value/Quantity as [Average Value]

Inline

[

  Value, Quantity

  1000, 4

  5000, 10

  3000, 3

  2000, 20

  1000, 5

  3000, 30

  5000, 2

];

NoConcatenate

Load

  Value,

  Quantity as TotalQty,

  (Quantity - IterNo() + 1) as [Individual Line Qty],

  [Average Value] as [Average Line Value]

Resident Test

While (Quantity - IterNo() + 1) > 0;

Drop Table Test;

[#8]

Unfortunately, this does not work.. The Values of the new field don't make any sense and the iterno() function does not work properly anymore. It counts from 1 to sum(all qty) for every value.