Calculated field in script

[reddwarfcrew]

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

[JaMajka1]

Could you post here an example?

[reddwarfcrew]

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

[JaMajka1 · Accepted solution]

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

[reddwarfcrew]

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

Field not found <ID>

[vishsaggi]

Can you paste your error window as a snapshot here?

[antoniotiman]

Main:

SQL SELECT ID, Value FROM Table1;

Supp:

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

SQL SELECT ID, Volume FROM Table2;

[JaMajka1]

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

[reddwarfcrew]

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

do I need to refer to bt.OBJID?

[JaMajka1]

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;