1 Reply Latest reply: Nov 21, 2014 4:27 AM by Peter Cammaert RSS

    SUB problem

    Dirk De Wispelaere

      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

      [F:\B-FS102\Qlikview\Telefonie\WOX\Datawarehouse\TEL_EQUIPMENT_LIST.qvd]

      (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

      [F:\B-FS102\Qlikview\Telefonie\WOX\Datawarehouse\TEL_UNIT_LIST.qvd]

      (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

      [F:\B-FS102\Qlikview\Telefonie\WOX\Datawarehouse\TEL_ORDERS_LIST.qvd]

      (qvd) Where FileDateTime=$(vTEL_ORDERS);

       

       

      LET vTEL_ORDERS=Peek('FilePath');

       

       

      DROP Table TEL_ORDERS;