topic Re: Include field from another table in load script in QlikView

Include field from another table in load script

jamestaylor52 — Thu, 04 Oct 2018 11:03:56 GMT

Hello,

Hopefully quite a simple one. I'm aware there's a method using applymap() but I can't get it working for the life of me! Any help appreciated.

I have two tables. [postage] is held in the [deal] table. However, I also need to pull it into [deal_mac_info] for various reasons. They are joined with [deal_mac_info_id-id] in the load script. The ultimate aim is to join them to a third table by concatenating a few fields.

[deal]:

LOAD

[id] AS [deal_id-id],

[postage],

[deal_mac_info_id] AS [deal_mac_info_id-id];

FROM `XXX`.`deal`;

[deal_mac_info]:

LOAD

[id] AS [deal_mac_info_id-id],

[commission],

[generic_channel],

[long_channel],

[media_cost],

[offer_type],

[vendor],

<<<<<<<--------------I want to include [postage] here from [deal] table;

FROM `XXX`.`deal_mac_info`;

Cheers in advance!

James

Re: Include field from another table in load script

Lisa_P — Fri, 05 Oct 2018 00:48:27 GMT

To pull postage in from deal table, declare it as a mapping table.

[deal_map]:

Mapping LOAD // mapping load is only available during script

[deal_mac_info_id] AS [deal_mac_info_id-id], // first field is the lookup

[postage] // second field is the one you want to pull in

FROM `XXX`.`deal`;

[deal_mac_info]:

LOAD

[id] AS [deal_mac_info_id-id],

[commission],

[generic_channel],

[long_channel],

[media_cost],

[offer_type],

[vendor],

Applymap('deal_map', deal_mac_info_id-id) as postage // pull the second field from mapping table if there is a // match on the lookup field

FROM `XXX`.`deal_mac_info`;

Hope this helps.

Lisa

Re: Include field from another table in load script

jamestaylor52 — Fri, 05 Oct 2018 09:35:54 GMT

Hi Lisa,

Thanks so much for your reply and detail.

I have tried the below and I get an error listed beneath - I think it's something easy but not sure what! I used Resident as the deal table is loaded beforehand but not sure if that's the issue?

----

[deal]:

LOAD

[id] AS [deal_id-id],

[name] AS [deal.name],

[postage],

[deal_mac_info_id] AS [deal_mac_info_id-id];

SQL SELECT `id`,

`name`,

`postage`,

`deal_mac_info_id`

FROM `XXX`.`deal`;

[deal_map]:

Mapping LOAD

[deal_mac_info_id-id] AS [deal_mac_info_id-id],

[postage]

Resident [deal];

[deal_mac_info]:

LOAD

[id] AS [deal_mac_info_id-id],

[commission],

[generic_channel],

[long_channel],

[media_cost],

[offer_type],

[vendor],

ApplyMap([deal_map],[deal_mac_info_id-id]) as Postage;

SQL SELECT `id`,

`commission`,

`generic_channel`,

`long_channel`,

`media_cost`,

`offer_type`,

`vendor`,

FROM `XXX`.`deal_mac_info`;

ERROR: ApplyMap error: map_id not found

Re: Include field from another table in load script

jamestaylor52 — Fri, 05 Oct 2018 09:54:08 GMT

(ignore the stray comma after vendor, it still gives the same error)

Re: Include field from another table in load script

tomcaridi — Fri, 05 Oct 2018 13:43:54 GMT

Try ApplyMap('deal_map',[deal_mac_info_id-id]) as Postage;

Re: Include field from another table in load script

jamestaylor52 — Fri, 05 Oct 2018 14:50:20 GMT

Thanks Tom. Unfortunately, now I get the error

Field 'deal_mac_info_id' not found

After I corrected to below

[deal]:

LOAD

[id] AS [deal_id-id],

[name] AS [deal.name],

[postage],

[deal_mac_info_id] AS [deal_mac_info_id-id];

SQL SELECT `id`,

`name`,

`postage`,

`deal_mac_info_id`

FROM `XXX`.`deal`;

[deal_map]:

Mapping LOAD

[deal_mac_info_id] AS [deal_mac_info_id-id],

[postage]

Resident [deal];

[deal_mac_info]:

LOAD

[id] AS [deal_mac_info_id-id],

[commission],

[generic_channel],

[long_channel],

[media_cost],

[offer_type],

[vendor],

ApplyMap('deal_map',[deal_mac_info_id-id]) as Postage;

Re: Include field from another table in load script

tomcaridi — Fri, 05 Oct 2018 14:54:36 GMT

You don't need to rename the field in the Mapping Load. Just use this:

[deal_map]:

Mapping LOAD

[deal_mac_info_id],

[postage]

Resident [deal];

Re: Include field from another table in load script

Miguel_Angel_Baeyens — Fri, 05 Oct 2018 15:03:05 GMT

If anything, it should be

ApplyMap('deal_map', [id]) AS Postage

The field [deal_mac_info_id-id] does not exist in the table [deal_mac_info] until it is completely loaded. [id] does exist and seems to be the one to use, by the looks of your script.

Re: Include field from another table in load script

QlikNoviceNo1 — Fri, 05 Oct 2018 15:05:56 GMT

Hi James,

1. In your mapping load, you're doing a resident load. So there is no need to rename the field [deal_mac_info_id] to [deal_mac_info_id-id]. Let it be [deal_mac_info_id-id] itself

[deal_map]:

Mapping LOAD

[deal_mac_info_id-id],

[postage]

Resident [deal];

2. Also, in the final applymap , do this - ApplyMap('deal_map',[id]) as Postage;