topic Re: Adding a calculated field in the script (may need use 'if' function) in QlikView

Adding a calculated field in the script (may need use 'if' function)

zlong1 — Wed, 05 Dec 2018 23:16:37 GMT

Hi there,

I have recently started using qlikview. I need some help.

As an example, there are 3 fields 'NAME' , 'SALES' and 'PERIOD':

NAME SALES PERIOD

A 100 0

A 200 1

B 100 0

B 300 1

How do I add one field in qlikview script and realize the following datatable:

NAME SALES PERIOD finalsale

A 100 0 100

A 200 1 300

B 100 0 100

B 300 1 300

As you see, when period =1, the finalsale is the sum of SALES from period = 0 and 1;

I have to sort the data in script, can anyone please tell me how to realize it.

Your assistance will be highly appreciated.

Thanks,

Loong

Re: Adding a calculated field in the script (may need use 'if' function)

zlong1 — Thu, 06 Dec 2018 00:00:24 GMT

Hi there,

There is a mistake in the second table.

It should be

NAME SALES PERIOD finalsale

A 100 0 100

A 200 1 300

B 100 0 100

B 300 1 400

Re: Adding a calculated field in the script (may need use 'if' function)

sunny_talwar — Thu, 06 Dec 2018 02:00:08 GMT

What will happen if you data is like this

NAME SALES PERIOD

A 100 0

A 200 1

A 300 0

A 400 1

B 100 0

B 300 1

What would be the output now?

Re: Adding a calculated field in the script (may need use 'if' function)

zlong1 — Thu, 06 Dec 2018 02:43:59 GMT

Hi Stalwar,

In my situation, there will not be duplicate name.

But if so,

the outcome should be

NAME SALES PERIOD final

A 100 0 400 (which is 100 + 300 )

A 200 1 1000 (which is 100 + 200 + 300+ 400)

A 300 0 400 (which is 100 + 300 )

A 400 1 1000 (which is 100 + 200 + 300+ 400)

B 100 0 100 keep 100

B 300 1 400 (which is 100 + 300 ) from B

Re: Adding a calculated field in the script (may need use 'if' function)

zlong1 — Thu, 06 Dec 2018 02:51:40 GMT

Or another simple example:

NAME SALES PERIOD

A 100 0

A 200 1

B 300 0

B 400 1

C 100 0

C 300 1

GO TO

NAME SALES PERIOD final

A 100 0 100

A 200 1 300(which is 100 + 200 )

B 300 0 300

B 400 1 700(which is 300+ 400)

C 100 0 100 keep 100

C 300 1 400 (which is 100 + 300 )

Re: Adding a calculated field in the script (may need use 'if' function)

zlong1 — Thu, 06 Dec 2018 03:04:50 GMT

or we can have another simple example

NAME SALES PERIOD
A 100 0
A 200 1
B 300 0
B 400 1
C 100 0
C 300 1

GO TO

NAME      SALES PERIOD final
A   100 0   100
A 200 1 300(which is 100 + 200 )
B 300 0 300
B 400 1 700(which is 300+ 400)
C 100 0 100 keep 100
C 300 1 400   (which is 100 + 300 )

Re: Adding a calculated field in the script (may need use 'if' function)

SharathPanackal — Thu, 06 Dec 2018 05:30:16 GMT

Hi Zlong1,

I am not sure this is a right and easy way to do but nevertheless should work:

TabA:
Load NAME&'|'PERIOD as Key,
NAME, SALES, PERIOD
From ..........;

TabB:
left join
Load Key, sum(SALES) as final
resident TabA
Group by Key;

Please mark as solution if this solution fixes the problem.

Best Regards,
Sharath Panackal

Re: Adding a calculated field in the script (may need use 'if' function)

sunny_talwar — Thu, 06 Dec 2018 12:16:02 GMT

@zlong1 wrote:

NAME      SALES PERIOD final
A   100 0   100
A 200 1 300(which is 100 + 200 )
B 300 0 300
B 400 1 700(which is 300+ 400)
C 100 0 100 keep 100
C 300 1 400   (which is 100 + 300 )

Why is this output different from the one above

Seems like the input is the same... but 4th row for A has 1000 in one case and 700 in another?

Re: Adding a calculated field in the script (may need use 'if' function)

zlong1 — Mon, 10 Dec 2018 00:17:05 GMT

Hi Stalwar,

The names are different in the two tables.

In my situation, there will not be a name with diverse value when period = 0.

When period = 0 . A will have only one value; when PERIOD =1 , A will have only one value too.

Therefore, I add a new name c into your table and change a little bit.

NAME SALES PERIOD final

A 100 0 100

A 200 1 300(which is 100 + 200 )

B 300 0 300

B 400 1 700(which is 300+ 400)

C 100 0 100 keep 100

C 300 1 400 (which is 100 + 300 )

D 5 0 5

D 7 1 12

E 20 0 20

E 30 1 50

......

The above table is the outcome what I want to realize.

Thanks a lot for your help.

Best,

Zlong

Re: Adding a calculated field in the script (may need use 'if' function)

sunny_talwar — Mon, 10 Dec 2018 12:07:21 GMT

Try something like this

Table:
LOAD * INLINE [
    NAME, SALES, PERIOD
    A, 100, 0
    A, 200, 1
    B, 300, 0
    B, 400, 1
    C, 100, 0
    C, 300, 1
    D, 5, 0
    D, 7, 1
    E, 20, 0
    E, 30, 1
];

FinalTable:
LOAD *,
	 If(NAME = Previous(NAME), RangeSum(Peek('Final'), SALES), SALES) as Final
Resident Table
Order By NAME, PERIOD;

DROP Table Table;