Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
mel_mesaoud
Contributor II

Select a field from another table in load script

Hi guys,

I am trying to insert this if statement into my load script

(if(invoice_customer = [Customer Number],val)) as 'spend'

The problem I have is that the field [Customer Number] is held within a different table.  So when I try to load the script this fails because the field is not found.

Is there a way this can be done?

Thanks

16 Replies
Highlighted
datanibbler
Esteemed Contributor

Re: Select a field from another table in load script

Hi Jamel,

not directly - you have to either put this field into a variable (if it has just one value) or join it into your primary table.

You can also try mapping that field. That way you won't have to actually join the tables.

See the blogpost on the issue of mapping instead of joining.

HTH

Highlighted

Re: Select a field from another table in load script

You may need to use Lookup or Apply map to achieve what you are trying to do. PFA a pdf which you might find useful (Page 7 for lookup and ApplyMap follows right after.

Best,

Sunny

Highlighted
buzzy996
Honored Contributor II

Re: Select a field from another table in load script

try with applymap().

Applymap('ur mapping table name',field_name in current table,'Missing') as new field

but u should use Mapping as a keyword on ur first table,tht's not the current table.

Highlighted
mel_mesaoud
Contributor II

Re: Select a field from another table in load script

Thanks for your help guys;

Here's what I've done

Created a 2nd Customers table

Customers2:

Mapping Load

  [Customer Number] as 'Customer Number',

  [Customer Name] as 'spend_customer_name'

FROM

  Data\dw_slcustm.qvd (qvd)

;

Tried this in my load script of the other table (Invoices)

ApplyMap('Customers2',[Customer Number],'Customer Number') as 'test'

Still doesn't work though

Highlighted
Partner
Partner

Re: Select a field from another table in load script

Hi,

You have to move both fields out of their parents table then do the calculation in a temporary table then left join the result back to whichever table you want to have the result from the calculation.

Highlighted
buzzy996
Honored Contributor II

Re: Select a field from another table in load script

some thing like this,

Customers2:

Mapping Load

  [Customer Number] as 'Customer Number',

  [Customer Name] as 'spend_customer_name'

FROM

  Data\dw_slcustm.qvd (qvd)

;

ur_second_table:

Load f1,

f2,

ApplyMap('Customers2',[Customer Number],'Customer Number') as 'test'

f4;

select

f1,

f2,

[Customer Number],

f4

from ur sourcetable;


first u have to execute Customers2 table and then u have to execute/load ur_second_table!



it should be work.

Highlighted
mel_mesaoud
Contributor II

Re: Select a field from another table in load script

Thanks guys but still can't get this working so I will forget this idea now and see what else I can come up with..

Highlighted
buzzy996
Honored Contributor II

Re: Select a field from another table in load script

are u tried the above one what i suggested?

Highlighted
mel_mesaoud
Contributor II

Re: Select a field from another table in load script

Hi Shiva,

I did try what you suggested but the field still wasn't recognised..