topic Re: Operation in script with two differnet field from different Table in QlikView

Operation in script with two differnet field from different Table

pascos88 — Tue, 08 Dec 2015 08:28:48 GMT

Hello,

I have this script

Now I just need a variable that for each Production for the second table, divide each N in the first table.

Someting like this : (AC/N) as Power. The fild come from the same table but in the first one I need to take just 1 row and in the second table I need to take 24 rows.

There is some way to reload the Leinstung in the second table or have this operation in a good way?

Thanks for help me

Regards

Pasquale

Re: Operation in script with two differnet field from different Table

Anonymous — Tue, 08 Dec 2015 08:31:00 GMT

Why you want to do at script?

Why not at UI?

Re: Operation in script with two differnet field from different Table

pascos88 — Tue, 08 Dec 2015 08:46:05 GMT

Hi Balraj,

Thanks for the response

I prefer to do this in a script becouse I need to use this operation a lot of time.

I need to do something like this :

(AC/N) as A

(AF/N) as B

...

In the chart I need to have also somethings like A-B/B..

You can see that it will be more easy to manage

Re: Operation in script with two differnet field from different Table

Anonymous — Tue, 08 Dec 2015 09:43:51 GMT

Well to do this, as per my knowledge..

First combine all the fields into one final table either by Join, Concatenation etc..

Then only you would be able to do it

Re: Operation in script with two differnet field from different Table

Colin-Albert — Tue, 08 Dec 2015 09:55:35 GMT

Use a mapping table

Company_N_map:

mapping load

NomeCompany,

resident LeinstungPark ;

drop table LeinstungPark ;

Then you can add this line to the IST table as a preceding load to get N into the IST table.

IST:

load

apply map('Company_N_map', NomeCompany) as N,

* ;

load

AB as Data

...

It would be easier to help if you posted the script as text rather than an image, then we can edit your script to show the solution.

Re: Operation in script with two differnet field from different Table

MayilVahanan — Tue, 08 Dec 2015 10:01:25 GMT

May be try like this

Load *, Production/NValue as NewProductionValue,

Revenue / NValue as NewRevenueValue;

Load *,

LookUp('Leinstung', 'NomeCompany', NomeCompany, 'LeinstungPark') as NValue

resident IST;

Drop table IST;

Re: Operation in script with two differnet field from different Table

Peter_Cammaert — Tue, 08 Dec 2015 10:05:54 GMT

Yes.

In the first table, you perform a Lookup of the first N value for every company (better replace the FOR loop with a GROUP BY and a WHERE Match()..., but that's beside your question). This means that every company has only one N value.

Store this in a mapping table composed of NomeCompany and N fields. Let's call that one MapComp2N.

In the second table, get a specific N value by performing an applymap() in this style:

...AC / applymap('MapComp2N', replace(replace(...))) AS ProductionDividedByN, ...

The second FOR loop can also be replaced by a LOAD WHERE Match or WHERE Exists() but again that's a different topic.

Best,

Peter

Re: Operation in script with two differnet field from different Table

pascos88 — Tue, 08 Dec 2015 13:41:23 GMT

Hi Peter,

thank for response.

I can't understand why you use the replace function in the applymap?

If I use mapping load in the first table of my example and use replace there in order to have the same NomeCompany, end if I use apply map in table 2 just like this:

ITS:

Load *,..... applymap('LeinstungPark', NomeCompany) as N

it will be works?

Re: Operation in script with two differnet field from different Table

Peter_Cammaert — Tue, 08 Dec 2015 14:13:14 GMT

Yes, of course that will work. Make sure that the Company names in the mapping table are the same as the ones you pass as second parameter in the applymap() call. I included the original replace calls to match your example code. Leave them out if you do not need them.

Best,

Peter

Re: Operation in script with two differnet field from different Table

Tue, 08 Dec 2015 16:53:08 GMT

The basic idea in Qlikview combine the these two tables into one table by either Join or ApplyMap approach.

After that perform your calculations add new fields. Please use proer keys to combine the tables otherwise you end up with ambiguous results.