Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rfreire06
Contributor III
Contributor III

ApplyMap function (No qualified path for file: ***)

Hi everyone!

So I have a series of tables created through scripting from an OLE DB. One of them contains data from customers (costumerID, costumerName and many other fields).

Now I'm importing data from an excel spreadsheet, and the table from there contains more data from customers, but their names is not there. Therefore, I would like to include a costumerName field based on an association to the first mentioned table.

I tried to do that using the ApplyMap function, but got the error mentioned on the title. It seems it is not possible to refer to another table previously created in the script. Any help on that would be appreciated!

1 Solution

Accepted Solutions
undergrinder
Specialist II
Specialist II

Hi Rhenan,

it seems the problem is due to previosly loaded table reference, not the ApplyMap function.

If you have already loaded a table you can refer to that with resident [table] not with from

.

Loading data

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/load-data-from-previousl...

ApplyMap help:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/MappingFunctions/ApplyMa...

G.

View solution in original post

11 Replies
swuehl
MVP
MVP

Could you post the Qlik script you are using?

undergrinder
Specialist II
Specialist II

Hi Rhenan,

it seems the problem is due to previosly loaded table reference, not the ApplyMap function.

If you have already loaded a table you can refer to that with resident [table] not with from

.

Loading data

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/load-data-from-previousl...

ApplyMap help:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/MappingFunctions/ApplyMa...

G.

rfreire06
Contributor III
Contributor III
Author

Hello Stefan,

I found a temporary solution, which was just associating the fields by giving them the same name. But since I have to do this more than once, it doesn't seem very wise. ApplyMap function seems to be the best call.

The script is divided in several sections. So I am posting the two ones that we are interested in and tried to remove unnecessary content as much as possible. The final part is commented, since I am currently using the alternative solution mentioned above.

\\FIRST SECTION

ENTIDADE:

Load

       [cdterritorio]               as [IDTerritorio]     

      ,[cdCidade] as [IDCidade]

      ,[cdCliente] as [IDCadastroGeral]

      ,[cdTpCliente]                as [IDTipoCliente]

       ,[cdCliente]   as %GrupoEmpresarial

     

     

      ,[cdCliente] as [IDCADASTRO]

      ,[cdCidade] as [IDCIDADE]

      ,[cdCliente] as [ENTIDADE Código do Cadastro]

      ,[cdRemessa] as [ENTIDADE cdRemessa]

      ,[cdfilialremessa] as [ENTIDADE cdfilialremessa]

      ,[Fax] as [ENTIDADE Fax]

      ,[cdEntidade] as [ENTIDADE Código]

      ,[tpEntCli] as [ENTIDADE Tipo de Entidade]  // A-Ambos, E - Fornecedro, C - Cliente.

      ,[nmEntCli] as [ENTIDADE Nome Fantasia]

      ,[tpPessoa] as [ENTIDADE Tipo Pessoa] // J-Juridica, I-Internacional, F-Fisica

      ,[RazaoSocial] as [ENTIDADE Razão Social]

      \\... MANY OTHER FIELDS

      ,[dtRenovacaoLicencaAFE] as [ENTIDADE Data Renovação Licença AFE]

      ,[cdClasseCliente] as [ENTIDADE Código Classe Cliente]

      ,[cdAtividadeCliente] as [ENTIDADE Código Atividade Cliente];

SQL

SELECT

       [cdCliente]

      ,[cdRemessa]

      ,[cdfilialremessa]

      ,[Fax]

      ,[cdEntidade]

      ,[tpEntCli]

      ,[nmEntCli]

      ,[tpPessoa]

      ,[RazaoSocial]

      ,[dtCadastro]

      ,[Fone1]

      ,[dtUltAtualiz]

      ,[Fone2]

      ,[cdCtaContabil]

      ,[CGC]

      ,[ie]

      ,[cdCidade]

      ,[Ativo]

      ,[dsEndereco]

     \\... MANY OTHER FIELDS

      ,[txMargemPrecoPO]

      ,[idTTD]

      ,[cdContaP]

      ,[dtRenovacaoLicencaAFE]

      ,[cdClasseCliente]

      ,[cdAtividadeCliente]

  FROM EntCli;

 

\\SECOND SECTION

CUSTOPROMOTORES:

LOAD

    "CÓD CLIENTE",

    GRUPO,

    UF,

    PROMOTOR,

    "CUSTO/VISITA",

    "VISITAS/SEMANA",

    "CUSTO TOTAL"

FROM [lib://Dados para DRE/Pasta2.xlsm]

(ooxml, embedded labels, table is Plan1);

CUSTOENTREGAS:

LOAD

    Coluna1,

    "Código do cliente" as "ENTIDADE Código do Cadastro",

    "Nº Pedido",

    "Valor do Pedido",

    "Nº NFE",

    "Valor NFE",

    "Separado por",

    "Tipo de Frete",

    "Entregue por:",

    "Valor do frete /  Diária agregado",

    "Quantidade  de entrega",

    "Valor por entrega",

    SIM,

    Coleta,

    "Nº Cotação",

    "Cotação R$",

    "% Cotação",

    "Data entrega/ coletado",

    Atendente,

    Obs,

    "OBS (CCE, Ordem coleta, data solicitada e etc)",

    Status,

    "Previsão de Entrega",

    Ocorrências,

    "PRAZO RECEB. NFE",

    CONFIRMAÇÃO,

    POSICIONAMENTO

FROM [lib://Dados para DRE/Pasta2.xlsm]

(ooxml, embedded labels, table is Plan2);

CUSTODESCONTOS:

LOAD

    Cod. as [IDCADASTRO],

    "Descontos Fixo em %"

FROM [lib://Dados para DRE/Pasta2.xlsm]

(ooxml, embedded labels, table is Plan3);

// MapCustomerIDtoName:

// Mapping LOAD [ENTIDADE Código do Cadastro], [ENTIDADE Nome Fantasia] From ENTIDADE ;

// CUSTOENTREGAS:

// LOAD *,

// ApplyMap('MapCustomerIDtoName', [ENTIDADE Código do Cadastro], null()) as [ENTIDADE Nome Fantasia]

// From CUSTOENTREGAS ;

rfreire06
Contributor III
Contributor III
Author

Hey Gabor,

That's what I thought! I had read the content from these 2 links before asking for help, but I couldn't make it work yet.

I am posting the script so you guys can have a look and possibly find out what I'm doing wrong.

balabhaskarqlik

Is the excel file path created properly.

You have to create a folder connection, since Qlik Sense works by default with lib connection.

Now, if you want to be able to enter your path as you did with no lib statement, you have to change the seetings file:

(under C:\Users\Your User\Documents\Qlik\Sense)

write StandardReload=0 (close QlikSense to be able to change this file).

Or post the screenshot, to refer where exactly you're facing the issue.

rfreire06
Contributor III
Contributor III
Author

Hi Bala,

I believe the excel file path was created correctly as I am able to use its data normally. The error occurs only when I try to use the ApplyMap function.

script error.jpg

balabhaskarqlik

In your above script you already created the ENTIDADE table in the initial.

At the bottom you want to create Mapping table on it. Then you could use Resident rather than From.

Change the script of Mapping:

Mapping LOAD [ENTIDADE Código do Cadastro], [ENTIDADE Nome Fantasia] From ENTIDADE ;

Like below:

Mapping LOAD [ENTIDADE Código do Cadastro], [ENTIDADE Nome Fantasia] Resident ENTIDADE ;

It resolves the issue.

juraj_misina
Luminary Alumni
Luminary Alumni

I think you missed the most important part of Gabor's response:

"If you have already loaded a table you can refer to that with resident [table] not with from [table]."

So in your script, when creating the mapping table, write

MapCustomerIDtoName:

Mapping LOAD [ENTIDADE Código do Cadastro], [ENTIDADE Nome Fantasia] Resident ENTIDADE //"resident" not "from"

Best

Juraj

undergrinder
Specialist II
Specialist II

Change the From to Resident, as I refer to my previous answer