Include field from another table in load script

[jamestaylor52]

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

[Lisa_P]

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

[jamestaylor52]

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?

----

1)

[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

[jamestaylor52]

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

[tomcaridi]

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

[jamestaylor52]

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;

[tomcaridi]

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

[Miguel_Angel_Baeyens]

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.

[QlikNoviceNo1]

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;