topic Re: Select a field from another table in load script in QlikView

Select a field from another table in load script

Anonymous — Fri, 19 Jun 2015 13:11:52 GMT

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

Re: Select a field from another table in load script

datanibbler — Fri, 19 Jun 2015 13:14:38 GMT

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

Re: Select a field from another table in load script

sunny_talwar — Fri, 19 Jun 2015 13:14:48 GMT

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

Re: Select a field from another table in load script

buzzy996 — Fri, 19 Jun 2015 13:33:31 GMT

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.

Re: Select a field from another table in load script

Anonymous — Fri, 19 Jun 2015 13:44:07 GMT

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

Re: Select a field from another table in load script

Gabriel — Fri, 19 Jun 2015 13:47:00 GMT

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.

Re: Select a field from another table in load script

buzzy996 — Fri, 19 Jun 2015 14:00:49 GMT

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],

from ur sourcetable;

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

it should be work.

Re: Select a field from another table in load script

Anonymous — Fri, 19 Jun 2015 14:13:31 GMT

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

Re: Select a field from another table in load script

buzzy996 — Fri, 19 Jun 2015 14:18:59 GMT

are u tried the above one what i suggested?

Re: Select a field from another table in load script

Anonymous — Fri, 19 Jun 2015 14:27:34 GMT

Hi Shiva,

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

Re: Select a field from another table in load script

buzzy996 — Fri, 19 Jun 2015 14:35:54 GMT

first u have to execute Customers2 table and then u have to execute/load ur_second_table on the same applciation/qvw! --are u able to do this way???

u can share screenshot of ur error msg?

Re: Select a field from another table in load script

Anonymous — Fri, 19 Jun 2015 15:16:25 GMT

Ok here goes;

This is the mapping load script

This is where i am using the applymap

This is shows you the tab orders

This is the error I am getting

Re: Select a field from another table in load script

buzzy996 — Fri, 19 Jun 2015 15:30:36 GMT

okie,

r u having [Customer Name] field information on ur dw_opsahistm.qvd file?

in custs2 table customer field having what kind of data,it's having customer names or customer ids?

Re: Select a field from another table in load script

Anonymous — Fri, 19 Jun 2015 15:36:03 GMT

Sorry, I have just changed my screenshots above because i want to use customer number and not customer name. I essentially would like to use the customer number field within the invoices table. Customer number is the customer ID number

Hopefully this will then allow me to use the below calculation within the invoices load script

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

Re: Select a field from another table in load script

Gabriel — Fri, 19 Jun 2015 15:50:37 GMT

Post a sample file (.qvw) with data for what you want to achieve

Re: Select a field from another table in load script

Anonymous — Wed, 24 Jun 2015 10:24:18 GMT

Hi guys,

I finally got the applymap working but the figures came out wrong ! Haha

Re: Select a field from another table in load script

vardhancse — Wed, 24 Jun 2015 10:36:27 GMT

Once check the data in mapping table, the values should be unique