SUB problem

dirk1955 — Fri, 21 Nov 2014 09:22:13 GMT

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;

Re: SUB problem

Peter_Cammaert — Fri, 21 Nov 2014 09:27:14 GMT

You can use TRACE statements to narrow down the exact spot where things start misbehaving.

topic Re: SUB problem in QlikView

SUB problem

Re: SUB problem