Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sspringer
New Contributor

Formula from 2 fields in different tables

If I have 2 tables, and want to do something like this:

if(Table1.Field1  <= Table2.Field2, 'Yes', 'No') as NewField - where would I do this in the script?  And how do I reference fields from 2 different tables?  I'd like to be able to add this as a new field to Table1.  Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Formula from 2 fields in different tables

Try

Table2:

LOAD * FROM Table2;

Table1:

LOAD

PricePaid,

ProductID,

if( PricePaid >= lookup('ContractPrice','ProductID',ProductID,'Table2') , 'Y', 'N') as PaidTooMuch,

...

FROM Table1;

So during the load of Table1, I lookup the ContractPrice in Table2 where ProductID of Table2 matches current ProductID in Table1 row. Table2 needs to be already loaded into QV (not necessarily all fields, but ProductID and ContractPrice.

Alternatively, if you only need these two columns from Table2, you could also do a mapping load of Table2 and use applymap() in your Table1 Load. The mapping table will not be part of your final data model, though.

Hope this helps,

Stefan

3 Replies
MVP
MVP

Re: Formula from 2 fields in different tables

Do you want to compare these two fields record-wise (i.e. line 1 to line1 etc.) or how do you want to get unambiguous values for a comparison? Do you have another Key field? Then you can use lookup() function.

Regards,

Stefan

sspringer
New Contributor

Re: Formula from 2 fields in different tables

Yes, there is a unique field across the 2 tables called ProductID.  I'm essentially looking in Table1 to see what was charged (PricePaid) and want to compare to Table2 (ContractPrice).  So, if Table1.PricePaid >= Table2.ContractPrice, then create a new field with "Y" or "N".


Thanks

MVP
MVP

Re: Formula from 2 fields in different tables

Try

Table2:

LOAD * FROM Table2;

Table1:

LOAD

PricePaid,

ProductID,

if( PricePaid >= lookup('ContractPrice','ProductID',ProductID,'Table2') , 'Y', 'N') as PaidTooMuch,

...

FROM Table1;

So during the load of Table1, I lookup the ContractPrice in Table2 where ProductID of Table2 matches current ProductID in Table1 row. Table2 needs to be already loaded into QV (not necessarily all fields, but ProductID and ContractPrice.

Alternatively, if you only need these two columns from Table2, you could also do a mapping load of Table2 and use applymap() in your Table1 Load. The mapping table will not be part of your final data model, though.

Hope this helps,

Stefan

Community Browser