Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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=...',
txt, codepage is 28591, no labels, delimiter is '\t', msq);
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.
Can do the from statement without the Data connection name in Standard mode in Qlik Sense
I meant Can't do the from statement without the Data connection name in Standard mode in Qlik Sense
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=...',
txt, codepage is 28591, no labels, delimiter is '\t', msq);