topic Re: How to calculate field in script after joining two tables in QlikView

How to calculate field in script after joining two tables

Fri, 25 Nov 2011 15:15:43 GMT

Can someone explain to me how to calculate a field in the script from two fields in a table that is the result of left-joining to load statements?

Here is the simplified script

Tab1:

Load

CustomerName,

InvoiceDate,

InvoiceValueEuro

From file1

Left Join Load

InvoiceDate,

ExchangeRate

From file2

What is the next step to add to this table the field InvoiceValueUSD=InvoiceValueEuro*ExchangeRate ??

How to calculate field in script after joining two tables

swuehl — Fri, 25 Nov 2011 19:06:29 GMT

A simple approach would be a resident load (or store to and load from a qvd) with adding the new field:

Result:

LOAD *, InvoiceValueEuro*ExchangeRate as InvoiceValueUSD

Resident Tab1;

drop table Tab1;

How to calculate field in script after joining two tables

swuehl — Fri, 25 Nov 2011 19:32:16 GMT

Or you could do a Mapping Load of your Exchangerate table first,

then use applymap function in creation of your new field while loading your value table.

How to calculate field in script after joining two tables

Wed, 30 Nov 2011 08:25:18 GMT

Thanks a lot for both answers. I have tried both solutions, and each works fine.

Re: How to calculate field in script after joining two tables

saradhakishore — Thu, 28 Mar 2019 07:01:53 GMT

Hi I have a similar query .i need to create a new table and the majority of the fields in this table are derived from a calculation based on couple of other resident table fields . how should i mention it in the script .please help