Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Fq
Partner - Contributor III
Partner - Contributor III

Load google spreadsheets with a cycle

Hi

Let's say I have different google spreadsheets files for different projects, they contain the same fields but different values.

I want to load them all, creating only one table.

I was thinking creating a config file like this:

Fq_2-1670595198290.png

 

And then when loading files from GSS (I already have configurated the web connector, the only thing I need is the Google Spreadsheet ID).

 

Creating a loop,

So...

 

Load

*,

$(ProjectName) AS Project

From ......$(FileID)...

 

And using the code only once, but looping over the values of the config table above.

 

Currently, I am loading separately each file and concatenating them, but I don't think is optimal.

 

Do you have any ideas in mind?

 

 

1 Solution

Accepted Solutions
rachel_delany
Creator II
Creator II

Yes this is doable.

Do something like this:

 

ConfigFile:

LOAD ProjectName, FileID FROM ConfigFile;

 

For each file in FieldValueList('FileID')

 

    TempProject:

    LOAD ProjectName

    Resident ConfigFile

    WHERE FileID = '$(file)';

 

   LET project = peek('ProjectName',1,'TempProject');

 

   ProjectData:

  Load

   *,

    '$(project)' AS Project

    From '$(file)';

 

    next;

View solution in original post

1 Reply
rachel_delany
Creator II
Creator II

Yes this is doable.

Do something like this:

 

ConfigFile:

LOAD ProjectName, FileID FROM ConfigFile;

 

For each file in FieldValueList('FileID')

 

    TempProject:

    LOAD ProjectName

    Resident ConfigFile

    WHERE FileID = '$(file)';

 

   LET project = peek('ProjectName',1,'TempProject');

 

   ProjectData:

  Load

   *,

    '$(project)' AS Project

    From '$(file)';

 

    next;