Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Michael_Tarallo
Employee
Employee

4-1-2021 1-33-58 PM.pngIn today's blog....as promised, Qlik Solution Architect Tomilayo Komolafe (Tomi) is back providing real in-the-trenches solutions to real customer problems. ( If you missed Tomi's introduction to the Qlik Design Blog and our last post / video,  you can visit this link - Contact Tracing with Qlik Sense SaaS - and Active Intelligence Solution.)

When working with a Qlik customer, Tomi stumbled upon a particular problem of not being able to iterate over, or loop through, file data stored in a cloud-based platform.  Tomi recorded a video of the particular solution, which I embedded below along with some sample data and a sample Qlik Sense App.(attached below) -  I would like to mention that Tomi borrowed some Qlik script for this solution from Qlik MVP and Luminary @stevedark  of Quick Intelligence fame. I would like to also thank Steve for his valuable contributions, not only to help make this solution easy and possible but also for all the help he provides to our Qlik Community members (reference material links below).  👍  - Thanks Steve and Tomi for demonstrating teamwork for results a Qlik core value to help our customers.

Before we get started - a word from our sponsor 😁

  • Don't forget to sign up for our next Do More with Qlik - next week 4/8 @ 2PM - where Jeff Goldberg and I will cover Qlik Sense SaaS and our new Webhooks integration.
    Register here:

The Problem

The problem was observed when recently working with a customer who needed to bring in 800+ csv files stored in his company’s OneDrive shared folder location into Qlik. If the files were stored in his local directory, he could use Qlik’s wildcard character to ingest many of these files. Something as simple as “*.csv” for example would have returned all files that are CSVs in his lib file path. Unfortunately, many of these cloud vendor options such as Google Drive, OneDrive, Dropbox, etc, don’t allow for use of special characters in their environment. Obviously, he would’ve needed to load all 800 CSV files individually right? 😉

Luckily, there’s a solution that automates this process in Qlik and still provides enough flexibility for the end user. Watch this video to learn how and note the links and reference materials below - including sample solution and data attached.

Can't see the video? YouTube blocked by your region or organization? Download LoopFiles.zip for a copy of the .mp4 file that you can download and watch on your computer or mobile device.

Reference: (Steve Dark solutions)

Files:

  • sample_data_and_app.zip
  • Extract .xls files to cloud-based storage location of your choice (OneDrive in this example)
  • Import .QVF file
  • Add / Edit Metadata / cloud data source connections appropriately

Qlik Help:

3 Comments
stevedark
MVP & Luminary
MVP & Luminary

Thanks for the mention and link to my posts. 

484 Views
Daniel_Pilla
Employee
Employee

Hey Mike!

I just wanted to share a much simpler method of achieving this goal, as I ran into it myself. For all of the storage connectors in QSESaaS (S3, OneDrive, Azure Blob) you should be able to execute a script like the following (without the need of using the specific Metadata connectors):

 

 

for each file in filelist('lib://Amazon_S3/')
    IF WildMatch('$(file)','*.qvd') THEN
      LOAD
          *
      FROM [$(file)](qvd);
    END IF
next file

 

 

 This is very straightforward, and you can adjust accordingly if you need to match on a naming convention or filetype. I just tested in OneDrive, and had no issues other than having to remove the trailing forward slash. That might vary by storage connector.

Hope you find it helpful!
Cheers,

365 Views
stevedark
MVP & Luminary
MVP & Luminary

Hi @Daniel_Pilla 

That is great news that this has now been implemented. It certainly wasn't there when I wrote my post, as I was porting clients form Client-Managed to Cloud and was having to re-write their loads due to FileList not being present. 

I guess that the following doesn't work, in the same way it does with a local file store?

for each file in filelist('lib://Amazon_S3/*.qvd')

 

It is a problem with the third party connectors that they all work ever so slightly differently, but this is the nature of having to plug in to the APIs that they expose.

Steve

0 Likes
270 Views