- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Tags:
- applymap
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
ApplyMap help:
G.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Could you post the Qlik script you are using?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
ApplyMap help:
G.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Change the From to Resident, as I refer to my previous answer
- « Previous Replies
-
- 1
- 2
- Next Replies »