topic Re: Web File Connection Issue in Connectivity & Data Prep

Web File Connection Issue

Rehan — Fri, 23 Apr 2021 19:41:26 GMT

We are using a FTP site to extract data . We are using Qlik Web Connectors to generate the link for us to add to Qlik Sense. We created a webfile connection like below

https://Servername:5555/data?connectorID=FileTransferConnector&table=FTPGetRawFile&host=somewebsite&ftpMode=FtpSslExplicit&port=21&userName=ABC&password=ABC%3acR478q%2fDtZgo4He2hJkrzw%3d%3d%3aMXvcdPg8ppnj2pnaB6%2frdQ%3d%3d&ignoreProxy=False&ignoreClientCertificateErrors=False&remotePath=%2fABC%2f20180813.bcp&appID=&loadAccessToken=Yd1hypk6VP'

It works fine. but we would like to make that connection dynamic meaning '20180813' above should be replaced by

date(today(),'YYYYMMDD')

How I can accomplish this cuz in the web file connector I can't add a variable.

Re: Web File Connection Issue

mfchmielowski — Sat, 24 Apr 2021 07:07:34 GMT

Hi.

You can use a variable there, just little preparation is needed.

let dateString = date(today(),'YYYYMMDD'); let connStringBase = 'https://Servername:5555/data?paramsHere'; let connStringBase = connStringBase & '&remotePath=%2fABC%2f' & dateString &'.bcp'; ftp: load * from [$(connStringBase)]; // I dont remember the full syntax of connector usage in qs

There's one thing in the loadAccessToken value. Last character is a singlequote sign. Is it a copy/paste here issue or is it a part of value ? Creating a connStringBase will require concatenatation of chr(39) function to have a singlequote char in the value.

Re: Web File Connection Issue

Rehan — Sat, 24 Apr 2021 14:00:18 GMT

Can do the from statement without the Data connection name in Standard mode in Qlik Sense

Re: Web File Connection Issue

Rehan — Sat, 24 Apr 2021 14:00:45 GMT

I meant Can't do the from statement without the Data connection name in Standard mode in Qlik Sense

Re: Web File Connection Issue

Rehan — Sun, 25 Apr 2021 22:24:47 GMT

Finally resolved it. Below are the steps.

Create a Web file connection using the base URL 'https://companyname.domain.com:5555/'

Let vDate=date(today(),'YYYYMMDD');

LOAD
@1,
@2,
@3,
@4,
@5,
@6
FROM [lib://Data-Connection]
(url is 'https://companyname.domain.com:5555/data?connectorID=FileTransferConnector&table=FTPGetRawFile&host=itas-reports.nih.gov&ftpMode=FtpSslExplicit&port=21&userName=Username&password=admin%3acR478q%2fDtZgo4He2hJkrzw%3d%3d%3aMXvcdPg8ppnj2pnaB6%2frdQ%3d%3d&ignoreProxy=False&ignoreClientCertificateErrors=False&remotePath=%2fTest%2f$(vDate).bcp&appID=&loadAccessToken=Yd1hypk6VP',
txt, codepage is 28591, no labels, delimiter is '\t', msq);