topic Re: Load a file in the last folder in QlikView

Load a file in the last folder

louisernould — Fri, 26 Sep 2014 13:09:37 GMT

Hello everybody,

I have a new problem and I think you guys will be able to solve it with me.

I have one folder (name: extract) , which is full of monthly extractions (names: 201407,201408,201409,...). In each folder, I have one file (name: Product.csv) and I want to select the file 'product' in the last folder (the new one) each month.

So, I want my script like that:

LOAD

from ['The file Product.csv from the last folder']

I tried something else with:

for each File in filelist ('G:Extract\*') But I don't know how to select the last folder...

If someone knows how to solve it... and wants to help a poor newbee

Thanks

Louis

Re: Load a file in the last folder

demoustier — Fri, 26 Sep 2014 13:25:31 GMT

Hi !

What is the rule nominating all your folder ?

How can we recognize the last folder: by his name ? his date ?

can you post a picture of your folder tree ?

Re: Load a file in the last folder

marcus_sommer — Fri, 26 Sep 2014 13:31:58 GMT

Beside filelist you could use dirlist to read folders and sub-folders - have a look in help by "for each ...".

- Marcus

Re: Load a file in the last folder

louisernould — Fri, 26 Sep 2014 13:33:08 GMT

hey Demoustier !

A tree is a good idea, but here is an explanation:

G:\Extract --201406-- file1.txt / file2.txt / Product.csv

--201407-- file1.txt / file2.txt / Product.csv

--201409-- file1.txt / file2.txt / Product.csv

--2014010-- file1.txt / file2.txt / Product.csv

For me, the newest is 201410 (october 2014) ans I want to load the file 'Product.csv' of the newest folder

Is it enough for you ?

Re: Load a file in the last folder

demoustier — Fri, 26 Sep 2014 13:45:54 GMT

OK.

Let's try 🙂

Could you try following script:

SUB DoDir(Root)

FOR each File in filelist (Root&'\*Product*'&'.csv')

FOR each Dir2 in dirlist (Root&'\*')

DIRNAME:

load

'$(Dir2)' as dirname

autogenerate 1;

CALL DoDir(Dir2)

Next File

NEXT Dir2

END SUB

CALL DoDir('G:\Extract')

it should return all folder adress in G where a .csv have Product in the name...

Does this step works ?

PS: I missed the red line in my first post

Re: Load a file in the last folder

Gysbert_Wassenaar — Fri, 26 Sep 2014 14:02:28 GMT

Something like this:

Temp:
load '' as DirName
AutoGenerate(0);

for each Dir in DirList('G:\Extract\*')

  SET vDir = $(Dir);
  Concatenate(Temp)
  load num(SubField('$(vDir)','\',-1)) as DirName AutoGenerate(1);

next Dir

Temp2:
LOAD max(DirName) as LastDir
Resident Temp;

drop Table Temp;

LET vLastDir = peek('LastDir');

drop Table Temp2;

Result:
LOAD * FROM  (txt, utf8, embedded labels, delimiter is ',', msq);

Re: Load a file in the last folder

louisernould — Fri, 26 Sep 2014 14:03:14 GMT

Ok this step works !

Sorry It's quite long ^^ and I needed to modify the script:

...

CALL DoDir(Dir2)

Next File

NEXT Dir2

Next File

END SUB

...

And It's good for now

Re: Load a file in the last folder

louisernould — Fri, 26 Sep 2014 14:09:36 GMT

Hi Gysbert !

Actually your solution gives to me a table with all the files in the subfolders (file1.txt, file2.txt and Product.csv)

I want only the newest product

Re: Load a file in the last folder

demoustier — Fri, 26 Sep 2014 14:09:58 GMT

2nd step:....use Gysbert solution !!! he's level 11 !!!!!!

next step depending of your 'dirname' list. could you give some result

Re: Load a file in the last folder

louisernould — Fri, 26 Sep 2014 14:11:23 GMT

ahahah ok good team job !

I try that !

Re: Load a file in the last folder

demoustier — Fri, 26 Sep 2014 14:14:35 GMT

or:

Step 2:

FOLDER_table:

load

right(dirname,len(dirname)-Index(dirname,'\',-1)) as folder_name Resident DIRNAME;

folder_list:

NoConcatenate

LOAD folder_name Resident FOLDER_table Order BY folder_name;

drop Table FOLDER_table;

and you should have now the list of your folder order by asc.

You chosse the last one by peek function in a variable and use the variable in your Load* from...

but not sure it's works. depending your folder_name list....

Re: Load a file in the last folder

demoustier — Fri, 26 Sep 2014 14:29:43 GMT

Final step should be:

let vfolder=Peek('folder_name',-1,'folder_list');

DATA:

LOAD * From 'G:\Extract\'$(vfolder)'\Products.csv'

Re: Load a file in the last folder

louisernould — Fri, 26 Sep 2014 15:05:36 GMT

Thank You so much desmoutier & Gysbert !!!!

It works soooo well

Thank you for your help