Operation in script with two differnet field from different Table

[pascos88]

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

[Anonymous]

Why you want to do at script?

Why not at UI?

[pascos88]

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

[Anonymous]

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

[Colin-Albert]

Use a mapping table

Company_N_map:

mapping load

    NomeCompany,

    N

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.

[MayilVahanan]

Hi

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;

[Peter_Cammaert · Accepted solution]

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

[pascos88]

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?

[Peter_Cammaert]

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

[#10]

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.