topic Re: Calculate 2 fields in script in QlikView

Calculate 2 fields in script

Tue, 17 Sep 2013 13:46:53 GMT

Hi community,

i load 2 tables in a script. E.g.

T1: contains field a

T2: contains filed b

now i want to subtract field a from field b as a new one. E.g. sum(a-b) as c.

Is this possible in my script?

Re: Calculate 2 fields in script

Gysbert_Wassenaar — Tue, 17 Sep 2013 13:57:03 GMT

That's only possible if you first combine the two tables into one table. You can use join for this.

Re: Calculate 2 fields in script

yevgeniy — Tue, 17 Sep 2013 14:20:57 GMT

T1: contains field a

join(T1)

T2: contains filed b

and need

T3:

load

a+b as c

resident T1

Re: Calculate 2 fields in script

Anonymous — Tue, 17 Sep 2013 14:40:19 GMT

HI,

Do you want to calculate all combinations possible of sum(a-b)?

If so, you can do

JOIN(T1)

LOAD

RESIDENT

T2;

You'll now have your field a and b in the table T1 and you can do:

Concatenate

(T1)

LOAD

sum(a-b)

RESIDENT

T1;

Otherwise, you should have at least one field in common in table T1 and T2 which will let you join field a to b.

Can you explain little more your need? And even join your qvw will be better.

Re: Calculate 2 fields in script

Wed, 18 Sep 2013 04:46:48 GMT

Hi,

i have one field in common in both tables. Its 'part_no'. In table T1 is the stock in table T2 is the safety stock.

And now i need the difference between stock and safety stock.

Thanks for you support guys.

Re: Calculate 2 fields in script

tresB — Wed, 18 Sep 2013 04:56:21 GMT

Try something like this:

Table1:

Load part_no,

Stock

From <your source>;

Join // left/right/inner whicever is applicable

Load part_no,

Safety

From <your source>;

NoConcatenate

Final:

Load *,

(Stock-Safety) as Difference

Resident Table1;

Drop Table Table1;

Re: Calculate 2 fields in script

rajeshvaswani77 — Wed, 18 Sep 2013 05:16:02 GMT

Hi,

Use applymap and bring the field you need from 2nd table to 1st table. Then do preceding load in 1st table and calculate the sum. That's the way I have done in many instances and it works fine.

thanks,

Rajesh Vaswani