3 Replies Latest reply: May 5, 2017 2:51 AM by Jonathan Dienst RSS

    Loop for multiple file is showing synthetic table....

    vir vir

      Hi,

       

      I am loading data from files by using below script:

       

      for i=$(vMin) to $(vMax)

       

       

      for each File in filelist( vCSVFile & '\AS_BrochureRequest_$(i).csv')

      Other:

       

       

       

      LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

               Right(FilePath,12) AS OtherFileName;

      LOAD '$(File)'   AS FilePath,

        recno() AS Otherrecord,

        'Input File' AS DataSource1,

        'Brochure' AS Type1

         

      FROM

      '$(File)'

      (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

       

       

      Next File

       

       

      Next i;

       

       

       

       

       

       

       

       

      for i=$(vMin) to $(vMax)

       

       

      for each File in filelist( vCSVFile & '\AS_GuestProfile_$(i).csv')

      NoConcatenate

      Other2:

      LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

               Right(FilePath,12) AS OtherFileName;

       

       

      LOAD '$(File)'   AS FilePath,

        recno() AS Otherrecord,

        'Input File' AS DataSource1,

        'Recipient' AS Type1

         

      FROM

      '$(File)'

      (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

       

       

      Next File

       

       

      Next i;

       

       

      but as final outcome i am getting synthetic table for each file please suggest how to resolve this issue.

        • Re: Loop for multiple file is showing synthetic table....
          Pradip Sen

          Try like below script:

           

          for i=$(vMin) to $(vMax)

            for each File in filelist( vCSVFile & '\AS_BrochureRequest_$(i).csv')

           

          Other:

          LOAD 'Brochure Request' as Source,

                   *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

                   Right(FilePath,12) AS OtherFileName;

          LOAD '$(File)'   AS FilePath,

            recno() AS Otherrecord,

            'Input File' AS DataSource1,

            'Brochure' AS Type1

            

          FROM

          '$(File)'

          (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

           

          Next File 

          Next i

           

          for i=$(vMin) to $(vMax)

            for each File in filelist( vCSVFile & '\AS_GuestProfile_$(i).csv')

           

          Other2:

          LOAD 'Guest Profile' as Source,

                    *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

                   Right(FilePath,12) AS OtherFileName;

           

           

          LOAD '$(File)'   AS FilePath,

            recno() AS Otherrecord,

            'Input File' AS DataSource1,

            'Recipient' AS Type1

            

          FROM

          '$(File)'

          (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

           

          Next File

          Next i

          • Re: Loop for multiple file is showing synthetic table....
            Prashant Sangle

            Hi,

             

            Why are you using noconcatenate???

             

            Your both table having same column??

             

            Regards,

            Prashant

            • Re: Loop for multiple file is showing synthetic table....
              Jonathan Dienst

              That's because you are loading the same field set into diferent tables, causing QV to create a composite key to associate the tables. You should load both sets of data into the same table, and then use a set expression with Type1 to select between the different sets.

               

              I suggest you load the data like this:

               

              Data:

              LOAD '' as FilePath AutoGenerate 0;

               

              For i= vMin to vMax

                Let vFile = '$(vCSVFile)\AS_BrochureRequest_$(i).csv';

                Concatenate(Data)

                LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

                Right(FilePath,12) AS OtherFileName;

                LOAD '$(vFile)'   AS FilePath,

                 recno() AS Otherrecord,

                 'Input File' AS DataSource1,

                 'Brochure' AS Type1

                FROM [$(vFile)]

                (txt, codepage is 1252, embedded labels, delimiter is '|', msq); 

               

                Let vFile = '$(vCSVFile)\AS_GuestProfile_$(i).csv';

                Concatenate(Data)

                LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

                Right(FilePath,12) AS OtherFileName;

                LOAD '$(vFile)'   AS FilePath,

                 recno() AS Otherrecord,

                 'Input File' AS DataSource1,

                 'Recipient' AS Type1  

                FROM [$(vFile)]

                (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

                

              Next i;