topic Creating new field in QlikView

Creating new field

Tue, 31 Mar 2015 07:26:43 GMT

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

Re: Creating new field

its_anandrjs — Tue, 31 Mar 2015 07:31:24 GMT

Hi,

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

Regards

Anand

Re: Creating new field

maxgro — Tue, 31 Mar 2015 07:34:34 GMT

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)<>'.' ;

load

[Average Value],

IterNo()

Resident

While 0+IterNo() <= Quantity;

Re: Creating new field

Tue, 31 Mar 2015 07:49:37 GMT

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.

Re: Creating new field

maxgro — Tue, 31 Mar 2015 07:52:17 GMT

maybe this?

load

[Average Value] as NameOfNewField,

IterNo()

Resident

While 0+IterNo() <= Quantity;

Re: Creating new field

pokassov — Tue, 31 Mar 2015 07:53:21 GMT

load

[Average Value] as [New avg],

IterNo()

Resident

While 0+IterNo() <= Quantity;

Re: Creating new field

MK_QSL — Tue, 31 Mar 2015 07:59:46 GMT

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;

Re: Creating new field

Tue, 31 Mar 2015 09:28:22 GMT

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.