Hi,
Don't know whats wrong whit this script.
I get no error message though he only creates 1 qvd instead of 3.
Only the QVD for the TEL_EQUIPMENT is created.
Purpose, I have 3 kinds of files in 1 directory on a server, i can not change that, i have to determ on a daily base which is the newest file and then i have to start working whit that file.
Any help?
//TEL_EQUIPMENT
Sub loadfiles(Files)
For Each file in FileList('$(Files)' & '\TEL_EQUIPMENT*.txt');
TEL_EQUIPMENT:
LOAD '$(file)' AS FilePath,
if(Len(SubField('$(file)','.',-1)) < 8,
SubField('$(file)','.',-1),'No Extension') AS FileExtension,
FileSize('$(file)') AS FileSize,
//FileName('$(file)') As FileName,
SubField('$(file)','\',(SubStringCount('$(file)','\'))+1) as FileName,
subfield(SubField(SubField('$(file)','\',(SubStringCount('$(file)','\'))+1),'_',3),'.',1) as FileDateTime,
FileTime('$(file)') AS FileTime
AutoGenerate 1;
NEXT file;
For Each subdir in DirList('$(Files)' & '\*');
CALL subFunction('$(subdir)')
NEXT subdir;
End Sub
SUB subFunction(Subdir)
For Each sub in DirList('$(Subdir)')
CALL loadfiles('$(sub)')
NEXT sub;
End Sub
Call loadfiles('F:\B-FS102\Qlikview\Telefonie\WOX\Data')
LET vTEL_EQUIPMENT=Peek('FileDateTime');
STORE TEL_EQUIPMENT into 'F:\B-FS102\Qlikview\Telefonie\WOX\Datawarehouse\TEL_EQUIPMENT_LIST.qvd' (qvd);
DROP Table TEL_EQUIPMENT;
TEL_EQUIPMENT:
LOAD FilePath,
FileExtension,
FileSize,
FileName,
FileDateTime,
FileTime
FROM
(qvd) Where FileDateTime=$(vTEL_EQUIPMENT);
LET vTEL_EQUIPMENT=Peek('FilePath');
DROP Table TEL_EQUIPMENT;
rem EXIT Script;
//TEL_UNIT
Sub loadfiles_TEL_UNIT(Files)
For Each file in FileList('$(Files)' & '\TEL_UNIT*.txt');
TEL_UNIT:
LOAD '$(file)' AS FilePath,
if(Len(SubField('$(file)','.',-1)) < 8,
SubField('$(file)','.',-1),'No Extension') AS FileExtension,
FileSize('$(file)') AS FileSize,
//FileName('$(file)') As FileName,
SubField('$(file)','\',(SubStringCount('$(file)','\'))+1) as FileName,
subfield(SubField(SubField('$(file)','\',(SubStringCount('$(file)','\'))+1),'_',3),'.',1) as FileDateTime,
FileTime('$(file)') AS FileTime
AutoGenerate 1;
NEXT file;
For Each subdir in DirList('$(Files)' & '\*');
CALL subFunction_TEL_UNIT('$(subdir)')
NEXT subdir;
End Sub
SUB subFunction_TEL_UNIT(Subdir)
For Each sub in DirList('$(Subdir)')
CALL loadfiles('$(sub)')
NEXT sub;
End Sub
Call loadfiles_TEL_UNIT('F:\B-FS102\Qlikview\Telefonie\WOX\Data')
LET vTEL_UNIT=Peek('FileDateTime');
STORE TEL_UNIT into 'F:\B-FS102\Qlikview\Telefonie\WOX\Datawarehouse\TEL_UNIT_LIST.qvd' (qvd);
DROP Table TEL_UNIT;
TEL_UNIT:
LOAD FilePath,
FileExtension,
FileSize,
FileName,
FileDateTime,
FileTime
FROM
(qvd) Where FileDateTime=$(vTEL_UNIT);
LET vTEL_UNIT=Peek('FilePath');
DROP Table TEL_UNIT;
exit Script;
//TEL_ORDERS
Sub loadfiles(Files)
For Each file in FileList('$(Files)' & '\TEL_ORDERS*.txt');
TEL_ORDERS:
LOAD '$(file)' AS FilePath,
if(Len(SubField('$(file)','.',-1)) < 8,SubField('$(file)','.',-1),'No Extension') AS FileExtension,
FileSize('$(file)') AS FileSize,FileName('$(file)') As FileName,
FileTime('$(file)') AS FileTime,
subfield(SubField(SubField('$(file)','\',(SubStringCount('$(file)','\'))+1),'_',3),'.',1) as FileDateTime,
FileName() as FileName
AutoGenerate 1;
NEXT file;
For Each subdir in DirList('$($(Files)' & '\*');
CALL subFunction('$(subdir)')
NEXT subdir;
End Sub
SUB subFunction(Subdir)
For Each sub in DirList('$(Subdir)')
CALL loadfiles('$(sub)')
NEXT sub;
End Sub
Call loadfiles('F:\B-FS102\Qlikview\Telefonie\WOX\Data')
LET vTEL_ORDERS=Peek('FileDateTime');
STORE TEL_ORDERS into 'F:\B-FS102\Qlikview\Telefonie\WOX\Datawarehouse\TEL_ORDERS_LIST.qvd' (qvd);
DROP Table TEL_ORDERS;
TEL_ORDERS:
LOAD FilePath,
FileExtension,
FileSize,
FileName,
FileDateTime,
FileTime
FROM
(qvd) Where FileDateTime=$(vTEL_ORDERS);
LET vTEL_ORDERS=Peek('FilePath');
DROP Table TEL_ORDERS;
