Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
datanibbler
Champion
Champion

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

sunny_talwar

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

buzzy996
Master II
Master II

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.

Anonymous
Not applicable
Author

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

Gabriel
Partner - Specialist III
Partner - Specialist III

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.

buzzy996
Master II
Master II

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.

Anonymous
Not applicable
Author

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..

buzzy996
Master II
Master II

are u tried the above one what i suggested?

Anonymous
Not applicable
Author

Hi Shiva,

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