Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
reddwarfcrew
Contributor II
Contributor II

Calculated field in script

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

1 Solution

Accepted Solutions
JaMajka1
Partner - Creator II
Partner - Creator II

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

View solution in original post

10 Replies
JaMajka1
Partner - Creator II
Partner - Creator II

Could you post here an example?

reddwarfcrew
Contributor II
Contributor II
Author

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
Partner - Creator II
Partner - Creator II

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
Contributor II
Contributor II
Author

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

Field not found <ID>

vishsaggi
Champion III
Champion III

Can you paste your error window as a snapshot here?

antoniotiman
Master III
Master III

Main:

SQL SELECT ID, Value FROM Table1;

Supp:

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

SQL SELECT ID, Volume FROM Table2;


JaMajka1
Partner - Creator II
Partner - Creator II

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

reddwarfcrew
Contributor II
Contributor II
Author

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

do I need to refer to bt.OBJID?

JaMajka1
Partner - Creator II
Partner - Creator II

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;