topic Re: How do you load from any folder? in QlikView

How do you load from any folder?

davyqliks — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How do you load from any folder?

RonaldDoes — Tue, 08 Aug 2017 14:03:09 GMT

Hi David,

How about one of these solutions: Load excel files from multiple folders

With kind regards,

Ronald

Re: How do you load from any folder?

marcus_malinow — Tue, 08 Aug 2017 14:03:18 GMT

Hi David,

you could use a loop through the results of a DirList(). Substitute in your starting folder, and away you go!

Marcus

For each dir in Dirlist('$(startingdir)\*')

NewSales:

LOAD *

FROM [$(dir)\uk.xlsx]

;

Next dir

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 14:15:37 GMT

Thank you for the answer, i have tried this

[\\FILE**\Support *****\**Files\New*****Dash\UK\Workflow\$(dir)\uk.xlsx]

and on reload i get this

****************Workflow\\uk.xlsx' The system cannot find the file specified.

can you see an error here. I did try this method before logging, thank you in advance

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 14:18:43 GMT

Thank you but from this it looks like replacing the folder name with /$/ this did not work for.

THank you for the link though, i will try to make sense of it.

Re: How do you load from any folder?

marcus_malinow — Tue, 08 Aug 2017 14:20:30 GMT

the issue here is that in your dir variable you have the full path, so change your path to just be

[$(dir)\uk.xlsx]

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 14:24:19 GMT

HI,

Thank you,

the trouble is the is multiple folders with UK.xlsx in, not just this one, i risk importing the wrong files.... The issue here with the workflow is the files are so big we split to year. This is the issue i am coming up against.

I hope you find this clear reasoning.

Re: How do you load from any folder?

marcus_malinow — Tue, 08 Aug 2017 14:34:21 GMT

Hi David,

I understand, try this loop

For each dir in Dirlist('\\FILE**\Support *****\**Files\New*****Dash\UK\Workflow\*')

NewSales:

LOAD *

FROM [$(dir)\uk.xlsx]

;

Next dir

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 14:50:13 GMT

please help to clarify my code:

I have the following. Are you suggesting i use the same path ' [$(dir)\uk.xlsx] ' for both? i think i am misunderstanding you.

Regards,

[InvoiceListing]:

LOAD [Inv NO.],

//[SO NO.],

//[PO NO.] as [PO PA SP],

[Customer NO.] as [Customer Name],

[Item Name],

Status,

CS as [Invoicee CS],

[Outstanding Amount] as [Invoice Total],

[Issue Date] as [Invoice Date],

Year([Issue Date]) as [INV Year],

Month([Issue Date]) as [Inv Month],

Day([Issue Date]) as [Inv Day],

Date(monthstart([Issue Date]), 'MMM-YYYY') as MonthYear,

//J

Filebasename() as Location

FROM

[\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Invoice\UK.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[WorkflowListing]:

LOAD [PO NO./PA NO.] as [PO PA SP],

//[Staff BarCode],

[Staff Name] as [Workflow User],

// [Staff Title],

[Steps BarCode],

//[Step Name],

[Step Desc],

// Action,

Date as [workflow Date],

Year([Date]) as [WF Year],

Month([Date]) as [WF Month],

Day([Date]) as [WF Day],

Time

//K

FROM

[\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Workflow\$(dir)\UK.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Re: How do you load from any folder?

marcus_malinow — Tue, 08 Aug 2017 15:02:01 GMT

Hi David,

Ok, so your WorkFlowListing load can be changed to enclose it in a loop:

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

[WorkflowListing]:

LOAD [PO NO./PA NO.] as [PO PA SP],

//[Staff BarCode],

[Staff Name] as [Workflow User],

// [Staff Title],

[Steps BarCode],

//[Step Name],

[Step Desc],

// Action,

Date as [workflow Date],

Year([Date]) as [WF Year],

Month([Date]) as [WF Month],

Day([Date]) as [WF Day],

Time

//K

FROM

[$(dir)\UK.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

next dir

I'm not so sure about your InvoiceListing as it looks to be retrieving from a different folder structure. Perhaps, if you have subfolder there as well you could enclose this load in another loop

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

Marcus

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 15:16:23 GMT

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

Re: How do you load from any folder?

effinty2112 — Tue, 08 Aug 2017 15:18:09 GMT

Hi David,

Maybe

For Y = 2014 to 2017

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

Next Y;

cheers

Andrew

Re: How do you load from any folder?

marcus_malinow — Tue, 08 Aug 2017 15:28:01 GMT

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

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 15:45:29 GMT

Thank you for all of your help.

I have not managed to resolve...yet!

Re: How do you load from any folder?

davyqliks — Tue, 08 Aug 2017 15:54:24 GMT

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

Re: How do you load from any folder?

effinty2112 — Tue, 08 Aug 2017 16:09:11 GMT

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

Re: How do you load from any folder?

MarcoWedel — Tue, 08 Aug 2017 20:11:05 GMT

see example 2 in:

For each..next ‒ QlikView

hope this helps

regards

Marco

Re: How do you load from any folder?

davyqliks — Wed, 09 Aug 2017 09:20:04 GMT

Thank you Andrew

Re: How do you load from any folder?

davyqliks — Wed, 09 Aug 2017 09:28:52 GMT

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,

Re: How do you load from any folder?

marcus_malinow — Wed, 09 Aug 2017 09:53:29 GMT

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