how to use ApplyMap in my joining script?

[ananyaghosh]

Hi,

I want to use ApplyMap in the below joining script:

[Temp Division]:
load
"Firm ID",
"Trn Key"
resident [F_SALES_TRN];
left join

LOAD
[Trn Key]
,"F_SALES_TRN_TERR_LIST.Terr Key" as [Terr Key]
resident [F_SALES_TRN_TERR_LIST];
left join
load
  Distinct
  "D_TERRITORY.Terr Key" as [Terr Key],
  [Terr Regn Mgr Last Name],
  [Terr Regn Mgr First Name],
  [Manager Wholesaler]   
resident [D_TERRITORY];
left join
LOAD
  Distinct
  "Firm ID",
  "Firm Channel Desc"
resident [D_FIRM];

Thanks,

Sandip

[Anil_Babu_Samineni]

What purpose you want? And which table you want to apply

[jmvilaplanap]

Hi

You can write this

MAP1:

Mapping

LOAD

  Distinct

  "Firm ID",

  "Firm Channel Desc"

resident [D_FIRM];

MAP2:

Mapping

load Distinct

  "D_TERRITORY.Terr Key",

  [Terr Regn Mgr Last Name] 

resident [D_TERRITORY];

MAP3:

Mapping

load Distinct

  "D_TERRITORY.Terr Key",

  [Terr Regn Mgr First Name] 

resident [D_TERRITORY];

MAP4:

Mapping

load Distinct

  "D_TERRITORY.Terr Key",

  [Manager Wholesaler]   

resident [D_TERRITORY];

[Temp Division]:

load

  ApplyMap('MAP1', "Firm ID") AS "Firm Channel Desc",

  "Trn Key"

resident [F_SALES_TRN];

left join

LOAD

  [Trn Key],

  ApplyMap('MAP2', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr Last Name] ,

  ApplyMap('MAP3', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr First Name] ,

  ApplyMap('MAP4', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Manager Wholesaler]

resident [F_SALES_TRN_TERR_LIST];

[jmvilaplanap]

Well, it will be better if you add a new map, in not necessary do a join just for one field

MAP1:

Mapping

LOAD

  Distinct

  "Firm ID",

  "Firm Channel Desc"

resident [D_FIRM];

MAP2:

Mapping

load Distinct

  "D_TERRITORY.Terr Key",

  [Terr Regn Mgr Last Name]

resident [D_TERRITORY];

MAP3:

Mapping

load Distinct

  "D_TERRITORY.Terr Key",

  [Terr Regn Mgr First Name]

resident [D_TERRITORY];

MAP4:

Mapping

load Distinct

  "D_TERRITORY.Terr Key",

  [Manager Wholesaler]  

resident [D_TERRITORY];

MAP5:

Mapping

load

  "Firm ID",

  "Trn Key"

resident [F_SALES_TRN];

[Temp Division]:

LOAD

  ApplyMap('MAP1', ApplyMap('MAP5',[Trn Key])) AS "Firm Channel Desc",

  ApplyMap('MAP2', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr Last Name] ,

  ApplyMap('MAP3', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr First Name] ,

  ApplyMap('MAP4', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Manager Wholesaler]

resident [F_SALES_TRN_TERR_LIST];

[marcus_malinow]

Sandip,

haven't you already covered this in a prior post? Time taken by resident load is huge

You're welcome by the way...

Marcus

[ananyaghosh]

Hi,

This only gives me id, not the corresponding column value:

[Temp Division]:

LOAD

  ApplyMap('MAP1', ApplyMap('MAP5',[Trn Key])) AS "Firm Channel Descs",

  ApplyMap('MAP2', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr Last Names] ,

  ApplyMap('MAP3', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr First Names] ,

  ApplyMap('MAP4', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Manager Wholesalers]

resident [F_SALES_TRN_TERR_LIST];

So I only getting the id values for "Firm Channel Descs" or [Terr Regn Mgr Last Names]  and I get IDs , but the corresponding String values for them.

Please help me in this case.

[ananyaghosh]

Hi,

This only gives me id, not the corresponding column value:

[Temp Division]:

LOAD

  ApplyMap('MAP1', ApplyMap('MAP5',[Trn Key])) AS "Firm Channel Descs",

  ApplyMap('MAP2', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr Last Names] ,

  ApplyMap('MAP3', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr First Names] ,

  ApplyMap('MAP4', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Manager Wholesalers]

resident [F_SALES_TRN_TERR_LIST];

So I only getting the id values for "Firm Channel Descs" or [Terr Regn Mgr Last Names]  and I get IDs , but the corresponding String values for them.

Please help me in this case.

[jmvilaplanap]

With this line in the script you will have the string

ApplyMap('MAP1', ApplyMap('MAP5',[Trn Key])) AS "Firm Channel Desc",

[ananyaghosh]

it does not work for me. Please give me a way to find out the fault in my code? I think we have used 'resident [F_SALES_TRN_TERR_LIST];' and "Firm Channel Desc" does not exists in that table. So it is not coming?

[jmvilaplanap]

I think, the name of the field was wrong, try with this to load the last table

[Temp Division]:
LOAD
ApplyMap('MAP1', ApplyMap('MAP5',[F_SALES_TRN_TERR_LIST.Terr Key])) AS "Firm Channel Desc",
ApplyMap('MAP2', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr Last Name] ,
ApplyMap('MAP3', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Terr Regn Mgr First Name] ,
ApplyMap('MAP4', "F_SALES_TRN_TERR_LIST.Terr Key") AS [Manager Wholesaler]
resident [F_SALES_TRN_TERR_LIST];