Qlik Cloud and folder data management

[Jack3]

Hi  everyone!

How could I store my qvd's and temporary csv's with Qlik Cloud in convenient tree folder structure, like it was possible with Qlik Sense Desktop?
Cloud Datafiles folder can't contain subfolders, so it instantly becomes a mess.
I can't connect directly to my local net shared folder, like in Qlik Desktop version.

I've tried Google drive, but it isn't possible to use a normal path to some folder on a Drive. Only the "id" of a path, wich looks like abracadabra hash string. Also it isn't possible to use wildcard and FileList function, as far as I know.
Of course, to load all data manually is not the option.

My company owns "Professional" Qlik license, and I've heard that with it you can create local server to store all your necessary data files. But I've failed to find any manuals about it.
Any help would be appreciated.

[johngouws]

Hello Jack. 

I use Dropbox for me RAW data as well as Transformed QVDs. To achieve this I have a number of folders with Sub folders in Dropbox. In QSB I create my LIB's to Dropboxx as normal. In the load script I define a few variable, for example: 

SET v_Company_Name = [Demo_]; // Reference which Company I am working with 
SET vRAW_QVDFolder = [lib://Dropbox/Sense_RAW_QVDs];
SET vCust_Location = [lib://Dropbox/GEO];

SET vTransform_Location = [lib://Dropbox/Transformed];

Then when loading or storing I can say something like: 

RAW:
LOAD
DocumentType&DocumentNumber as _KEY_HH_HL,
OrderNumber,......
FROM [$(vRAW_QVDFolder)/$(v_Company_Name)Extract.qvd](qvd);

or 

STORE RAW INTO [$(vTransform_Location)\Fact_Transformed.qvd];

You will need to give it some thought, but I think you can achieve a reasonable solution. 

BTW: Using similar logic, I have been able to move all me scripts into qvs files. 

I hope this helps a bit. 

[Jack3]

Yeah, it helps! Dropbox has low chargeless size limit, but at least it provides the convenient addressing way (with subfolders names and slashes).
Unfortunately, Dropbox has the same restriction as Google Drive: "Wildcard/masking syntax is not supported. You may only load specified individual files, or all files in a specified folder." (link: official qlik help )

What are the benefits of using qvs? Now I store every script in separate qvf file.

[johngouws]

Ahh. Yes, I forgot about the wildcard issue. Fortunately all my ETLs are handled on prem with good old QlikView, thereby presenting already consolidated QVDs to Dropbox. If a transaction table gets big I produce a TX_History QVD, say to the end of 2019 and only bring in transactions for 2020 on a daily bases. In the QSB scripts I concatenate the QVDs. 

I like having the scripts in qvs files for a number of reasons. One is I can back them up separately to the App. From the QV days, if something with wrong with the model I still had the scripts. 

[johngouws]

Morning. 

A short while back there were similar questions with regards to loading excel files. @stevedark gave a very clear solution for these questions. This morning I made very small modifications to enable the loading of multipile QVDs.  Below is the script I am using for loading my QVDs: 

LIB CONNECT TO 'Dropbox_Metadata';
LET vCompany = 'Test_';

/********************************************************************************************************/
/***** I have various Companies qvds in a Dropbox folder. Qvds are prefixed with vComnany var ******/
/***** In my Test folder I have 2 qvds. 1 for historic transactions and 1 for current transactions ******/
/***** They have the exact same field names ******/
/***** In my case I use a _ to seperate the Company name from the qvd name ******/
/********************************************************************************************************/

tmpFiles:
LOAD
path_lower
WHERE left(name,5) = '$(vCompany)' and
right(path_lower, 3) = 'qvd'
;
SELECT
path_lower,
name
FROM List
WITH PROPERTIES (
dropboxFolder='/Test', /*REMEMBER YOUR DROPBOX FOLDER NAME*/
recursive='false',
includeDeleted='false'
);

for iFile = 0 to NoOfRows('tmpFiles') - 1
let vFile = peek('path_lower', iFile, 'tmpFiles');

LOAD
* /*OR spefify your field names*/
FROM [lib://Dropbox$(vFile)](qvd);

next

DROP TABLE tmpFiles;

exit script;

To read more from Steve: https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/

Hope this helps. Stay Safe....

[Jack3]

So, the most easy way to continue getting my xlsx data from local net folder, after Qlik Desktop support will be over - is to install QlikView and create a script to download qvd's to the Google Drive or the Dropbox?

Or, may be, write a script (e.g. in Python) which will upload xlsx files directly onto G_Drive/Dropbox?

[johngouws · Accepted solution]

They are all options available to you. It depends what you want to do and what is best for you. You can do the QlikView to QVD thing if that works for you. You can also have your Excel files into a Dropbox folder and use the below script. (modified for a xlsx file) to read the Excel files: 

LIB CONNECT TO 'Dropbox_Metadata';
LET vCompany = 'Test_';

/********************************************************************************************************/
/***** I have various Companies xlsx files a Dropbox folder. Xlsxs's are prefixed with vComnany var ******/
/***** In my Test folder I have 2 xlsx files. 1 for historic transactions and 1 for current transactions ******/
/***** They have the exact same field names ******/
/***** In my case I use a _ to seperate the Company name from the xlsx name ******/
/********************************************************************************************************/

tmpFiles:
LOAD
path_lower
WHERE left(name,5) = '$(vCompany)' and
right(path_lower, 4) = 'xlsx'
;
SELECT
path_lower,
name
FROM List
WITH PROPERTIES (
dropboxFolder='/Test', /*REMEMBER YOUR DROPBOX FOLDER NAME*/
recursive='false',
includeDeleted='false'
);

for iFile = 0 to NoOfRows('tmpFiles') - 1
let vFile = peek('path_lower', iFile, 'tmpFiles');

LOAD
* /*OR spefify your field names*/
FROM [lib://Dropbox$(vFile)](qvd);

next

DROP TABLE tmpFiles;

exit script;