topic Calculated field in script in QlikView

Calculated field in script

reddwarfcrew — Mon, 16 Oct 2017 19:43:30 GMT

Hi,

I have 2 SQL Select statements from an oracle db in a script.

I'd like to create a calcuated field based on values from each of the 2 seperate SQL scripts. I know how to do this as a calculated dimension, but I'd like to use it in multiple objects in the report, so would prefer to put it into the load or have as some sort of global calculated dimesion.

Any ideas?

Thanks

Re: Calculated field in script

JaMajka1 — Mon, 16 Oct 2017 20:16:21 GMT

Could you post here an example?

Re: Calculated field in script

reddwarfcrew — Mon, 16 Oct 2017 20:20:09 GMT

Sure,

Main:

SQL SELECT ID, Value FROM Table1;

Supp:

SQL SELECT ID, Volume FROM Table2;

I'd like to add a calculated field that is Value * Volume.

The actual SQL for each is a lot more complex so I can't easily join the 2 queries into 1 SQL statement and perform the calculation in the 1 SQL.

Thanks

Re: Calculated field in script

JaMajka1 — Mon, 16 Oct 2017 20:27:06 GMT

I would prefer to load these two tables separatly from SQL and calculate the Value*Volume in Qlik load script.

NoConcatenate

[data]:

SQL Select ID, Value From Table1;

Left join (data) // or another type of join

SQL Select ID, Volume From Table 2;

NoConcatenate

[final]:

LOAD

ID,

Value*Volume as Measure

Resident data;

Drop table data;

BR,

Maria

Re: Calculated field in script

reddwarfcrew — Mon, 16 Oct 2017 20:58:35 GMT

Thanks, I've tried that but the script fails with:

Field not found <ID>

Re: Calculated field in script

vishsaggi — Mon, 16 Oct 2017 21:28:29 GMT

Can you paste your error window as a snapshot here?

Re: Calculated field in script

antoniotiman — Mon, 16 Oct 2017 22:11:28 GMT

Main:

SQL SELECT ID, Value FROM Table1;

Supp:

LOAD *,Lookup('Value','ID',ID,'Main')*Volume as NewField;

SQL SELECT ID, Volume FROM Table2;

Re: Calculated field in script

JaMajka1 — Tue, 17 Oct 2017 05:03:23 GMT

Are you sure that in your SQL tables does the field ID exist?

Re: Calculated field in script

reddwarfcrew — Tue, 17 Oct 2017 08:15:29 GMT

in my SQL it is SQL Select bt.OBJID as ID

do I need to refer to bt.OBJID?

Re: Calculated field in script

JaMajka1 — Tue, 17 Oct 2017 10:11:06 GMT

Sure, you need to use the same name as it is in the DB. Do you want to also rename the field?

NoConcatenate

[data]:

SQL Select OBJID, Value From Table1;

Left join (data) // or another type of join

SQL Select OBJID, Volume From Table 2;

NoConcatenate

[final]:

LOAD

OBJID, // or OBJID as ID, if you want to rename the field

Value*Volume as Measure

Resident data;

Drop table data;

Re: Calculated field in script

reddwarfcrew — Tue, 17 Oct 2017 17:40:33 GMT

Finally sussed the problem. For some reason, the script didn't like the field name having an _ in it. I renamed the field to remove the _ and it worked!!