Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
somthingmendi123
Contributor III
Contributor III

concatenat and loop

In honor of the dear Click Sense developers
I have tables of goals for 2018 and 2019 in the model, which I chained, but I want to create a situation where every new goal file that arrives in a folder on the computer will be automatically chained to the model. That is, I have to create a loop and connection of all the data in an orderly manner.

I want to generate the script so that it is generic. that if a file from the year 2020 / 2021 is added for example to the folder - he will upload it to me as well.
I need to use a loop that will extract the year from the name of the file into a variable and use the variable to extract the appropriate tab from Excel.

How do I write the script?

Labels (1)
3 Replies
udit_k
Partner - Creator II
Partner - Creator II

Below script works for sharepoint as source for excel files:-

let vQwcConnectionName = 'lib://SharePoint_Connection_Test';
let vgetfileconnection = 'lib://GetExcelFiles';

Office365Connector_ListFiles:
LOAD
    UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [web connector URL, it tripped the spam filter so removing it], qvx);

for i=0 to NoOfRows('Office365Connector_ListFiles') - 1
let vField = Peek('ListFiles_UniqueId',$(i),'Office365Connector_ListFiles');
LOAD
    *
FROM [$(vgetfileconnection)$(vField)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;
NEXT i

somthingmendi123
Contributor III
Contributor III
Author

I thank you very much for the answer and I am starting to work with the code and try to solve my problem
Could you please explain more what each line of code does?
Thank you

udit_k
Partner - Creator II
Partner - Creator II

Hello , 

Please find comments against the lines to define code.

let vQwcConnectionName = 'lib://SharePoint_Connection_Test';          //SharePoint Connection String
let vgetfileconnection = 'lib://GetExcelFiles';        //Excel Files connection string

Office365Connector_ListFiles:           //Table to fetch nos of excel files, there unique id
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [web connector URL, it tripped the spam filter so removing it], qvx);

for i=0 to NoOfRows('Office365Connector_ListFiles') - 1   // loop to itereate for fetching details from each excel file
let vField = Peek('ListFiles_UniqueId',$(i),'Office365Connector_ListFiles');            //Name of each excel file
LOAD
*
FROM [$(vgetfileconnection)$(vField)]             //Loading excel files from source
(txt, utf8, embedded labels, delimiter is ',', msq)
;
NEXT i