Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
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.
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.
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..
are u tried the above one what i suggested?
Hi Shiva,
I did try what you suggested but the field still wasn't recognised..