Solved: Load a file in the last folder - Qlik Community

louisernould · ‎2014-09-26

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

@1

@2

@3

@4

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

demoustier · ‎2014-09-26

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....

View solution in original post

demoustier · ‎2014-09-26

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 ?

marcus_sommer · ‎2014-09-26

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

- Marcus

louisernould · ‎2014-09-26

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 ?

demoustier · ‎2014-09-26

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

Gysbert_Wassenaar · ‎2014-09-26

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);

talk is cheap, supply exceeds demand

louisernould · ‎2014-09-26

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

louisernould · ‎2014-09-26

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

demoustier · ‎2014-09-26

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

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

louisernould · ‎2014-09-26

ahahah ok good team job !

I try that !