topic Help to create a field dynamicaly in QlikView

Help to create a field dynamicaly

Wed, 18 Jun 2014 07:50:29 GMT

Hi Everyone,

I have two fields date and blnc

Date	Balance
1/04/2014	856
2/04/2014	147
3/04/2014	259
4/04/2014	325
8/04/2014	693
9/04/2014	258

I have to add two new field CurrentAcc and InitialAcc.

initially InitialAcc=0,

and Expression for CurrentAcc=Balance+InitialAcc.

And for next date InitialAcc=CurrentAcc.

I know I have to use a loop statements but how I use the expression in the back end. If anyone has any idea do share with me.

Thank you in advance.

Re: Help to create a field dynamicaly

Wed, 18 Jun 2014 07:55:00 GMT

use the RowNo() and Peek() functions, the examples are quite good it

Re: Help to create a field dynamicaly

Wed, 18 Jun 2014 07:58:01 GMT

But how I store the value in the field,do you have any script idea for it. since I am very new to qlikview so I didnt get the right way to do.

Re: Help to create a field dynamicaly

MK_QSL — Wed, 18 Jun 2014 07:59:31 GMT

Use below script..

===================

Temp:

Load

RowNo() as NO,

Date(Date#(Date,'D/MM/YYYY')) as Date,

Balance

Inline

[

Date, Balance

1/04/2014, 856

2/04/2014, 147

3/04/2014, 259

4/04/2014, 325

8/04/2014, 693

9/04/2014, 258

];

Final:

Load

NO,

Date,

Balance,

If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc

Resident Temp

Order By NO;

Drop Table Temp;

//Drop Field NO

=====================================

Re: Help to create a field dynamicaly

selvakumarsr — Wed, 18 Jun 2014 08:04:23 GMT

Try this..

RangeSum(Above(Sum(Balance), 0, RowNo()))...

Selva

Re: Re: Help to create a field dynamicaly

Wed, 18 Jun 2014 08:09:54 GMT

Date	Balance	InitAcc	CurrAcc
1/04/2014	856	0	856
2/04/2014	147	856	1003
3/04/2014	259	1003	1262
4/04/2014	325	1262	1587
8/04/2014	693	1587	2280
9/04/2014	258	2280	2538

This script is not giving the right answer. I want a new table with above field..

Re: Re: Help to create a field dynamicaly

MK_QSL — Wed, 18 Jun 2014 08:15:43 GMT

Temp:

Load

RowNo() as NO,

Date(Date#(Date,'D/MM/YYYY')) as Date,

Balance

Inline

[

Date, Balance

1/04/2014, 856

2/04/2014, 147

3/04/2014, 259

4/04/2014, 325

8/04/2014, 693

9/04/2014, 258

];

Final:

Load

NO,

Date,

Balance,

If(RowNo() = 1, 0, Peek(CurrentAcc)) as InitAcc,

If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc

Resident Temp;

Drop Table Temp;

Re: Help to create a field dynamicaly

Wed, 18 Jun 2014 08:25:33 GMT

Manish has the answer you are looking for

Temp:

Load

RowNo() as NO,

Date(Date#(Date,'D/MM/YYYY')) as Date,

Balance

Inline

[

Date, Balance

1/04/2014, 856

2/04/2014, 147

3/04/2014, 259

4/04/2014, 325

8/04/2014, 693

9/04/2014, 258

];

Final:

Load

NO,

Date,

Balance,

If(RowNo() = 1, 0, Peek(CurrentAcc)) as InitAcc,

If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc

Resident Temp;

Drop Table Temp;