topic Re: how to use ApplyMap in my joining script? in QlikView

how to use ApplyMap in my joining script?

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: how to use ApplyMap in my joining script?

Anil_Babu_Samineni — Thu, 29 Jun 2017 13:38:20 GMT

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

Re: how to use ApplyMap in my joining script?

jmvilaplanap — Thu, 29 Jun 2017 13:39:36 GMT

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

Re: how to use ApplyMap in my joining script?

jmvilaplanap — Thu, 29 Jun 2017 13:44:05 GMT

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

Re: how to use ApplyMap in my joining script?

marcus_malinow — Thu, 29 Jun 2017 14:21:38 GMT

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

Re: how to use ApplyMap in my joining script?

ananyaghosh — Tue, 04 Jul 2017 06:30:43 GMT

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.

Re: how to use ApplyMap in my joining script?

ananyaghosh — Tue, 04 Jul 2017 06:37:19 GMT

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.

Re: how to use ApplyMap in my joining script?

jmvilaplanap — Tue, 04 Jul 2017 08:29:32 GMT

With this line in the script you will have the string

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

Re: how to use ApplyMap in my joining script?

ananyaghosh — Tue, 04 Jul 2017 08:44:08 GMT

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?

Re: how to use ApplyMap in my joining script?

jmvilaplanap — Tue, 04 Jul 2017 09:07:00 GMT

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

Re: how to use ApplyMap in my joining script?

ananyaghosh — Wed, 05 Jul 2017 10:03:38 GMT

hi,

I have tried it in many ways , but I failed, as because '"F_SALES_TRN_TERR_LIST.Terr Key" means the column name used in ApplyMap function , is different than Mapping column name. So it will not work.

Thanks,

Sandip

Re: how to use ApplyMap in my joining script?

jmvilaplanap — Wed, 05 Jul 2017 11:07:30 GMT

Sorry, my last comment is wrong, the double map is like the first one comment

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

And the MAP5 is in the wrong order (now is good)

MAP5:

Mapping

load

"Trn Key",

"Firm ID",

resident [F_SALES_TRN];

Try with this code:

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
"Trn Key",
"Firm ID",
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];