How do you load from any folder?

[davyqliks]

HI,

We are trying to load uk.xlsx from multiple folders.

The idea is that we use filebase name to give us a location code.

So, we have the following folders

NewSales/UK/2014/uk.xlsx

NewSales/UK/2015/uk.xlsx

NewSales/UK/2016/uk.xlsx

NewSales/UK/2017/uk.xlsx

I was hoping, rather than repeat the load script that i could have something like

NewSales/UK/*/uk.xlsx

but this is not recognised. Is there code to allow me to load from multiple folder? What would i replace the /*/ with please.

Thank you in advance

[davyqliks]

I think i have a problem here...

The QV doc is on a different server to the files, when just trying to import the Workflow using

FROM

[$(dir)\UK.xlsx]

The error not finding the file refers to the server where the QV doc is. The files ( in multiple named  folders all with UK.xlsx) are on a different server along with the Folders with HK.xlsx in.  The idea is to separate data by folder and name by location.

Is the different server for the files the issue here?

I am still unsure how workflow will point to the correct UK.xlsx if just using this..

FROM

[$(dir)\UK.xlsx]

Thank you, your help is very much appreciated

[effinty2112]

Hi David,

Maybe

For Y = 2014 to 2017

Load * From NewSales/UK/$(Y)/uk.xlsx;

Next Y;

cheers

Andrew

[marcus_malinow · Accepted solution]

Ok, so it sounds like the dirlist() command may be failing.

There shouldn't be any issue with the files being located remotely, as long as they are in a browseable share, and your user id has access to them. Try opening windows explorer on your qlik machine and browsing to the root folder containing your excel files.

I am still unsure how workflow will point to the correct UK.xlsx if just using this..

FROM

[$(dir)\UK.xlsx]

The 'For each dir in Dirlist('\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Invoice\*')' will set up dir to the full path of each directory within the '\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Invoice\' directory

[davyqliks]

Thank you for all of your help.

I have not managed to resolve...yet!

[davyqliks]

Hi,

Thank you for the suggestion, i do not understand your answer.

How do you mean For Y = 2014 to 2017

please explain how you feel i should layout in the folder structure and how to reference in the load script.

Please remember, i have multiple folders in a location like below

UK>Workflow>2017>UK.xlsx

UK>Workflow>2016>UK.xlsx

UK>Invoice>UK.xlsx

UK>Sales>UK.xlsx

UK>PO>UK.xlsx

UK>SO>UK.xlsx

for the bottom 4, obviously i can just point to the file but for the workflow. I want to have a load script that looks at all

UK> Workflow> insert something here  >UK.xlsx

The objective being that i do not want to have 4 different load scripts for the 4 folders as the data is all in the same format.

Thank you again for considering this for me

[effinty2112]

Hi David,

I was responding to your question as posed

So, we have the following folders

NewSales/UK/2014/uk.xlsx

NewSales/UK/2015/uk.xlsx

NewSales/UK/2016/uk.xlsx

NewSales/UK/2017/uk.xlsx

I think Marcus is pointing you in the right direction if your folders are not as quoted above.

Good luck

Andrew

[MarcoWedel]

see example 2 in:

For each..next ‒ QlikView

hope this helps

regards

Marco

[davyqliks]

Thank you Andrew

[davyqliks]

Thank you all for your assistance but i cannot make sense of example 2.

I have decided to create 2 loads

1 for historic and 1 for YTD.

I have marked Marcus as correct although unable to implement.

Regards,

[marcus_malinow]

Ok, I'd suggest breaking this down.

First off, what's the full UNC path to the folder containing all your Workflow folders? On your Qlik server, try opening Windows explorer and browsing to this path. If it's not browseable, your code won't work. You may need to look into setting up the sharing permissions.

Next off, try running this code, substituting in the correct path, and terminating the string with '\*':

For each dir in Dirlist('\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Workflow\*')

    TRACE $(dir);

Next dir

This should output a list of the subdirectories into the Script Execution Progress window