Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What purpose you want? And which table you want to apply
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];
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];
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
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.
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.
With this line in the script you will have the string
ApplyMap('MAP1', ApplyMap('MAP5',[Trn Key])) AS "Firm Channel Desc",
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?
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];