4 Replies Latest reply: Dec 29, 2017 2:59 AM by Anton Wallmark RSS

    Load one file and store it in multiple files

    Anton Wallmark

      Hi,

       

      I have used QlikSense to load data from a .csv file and give the columns titles and then I have stored it as a .txt file before. This was no problem. Now however, I have one very large file (.csv) containing data over several months. I need to load the file (.csv), give the columns names and instead of storing the file into one large .txt as before, I would like to store the file into several different .txt-files, one for each month.

       

      Is this possible?

       

      For example:

       

      LOAD

      @1 as year

      @2 as month

      @3 as product

       

      FROM [myfile.csv]

       

      store * from [myfile] into [lib://test/mynewfile.txt]

       

      This one works fine. Now however, I want something like this:

       

      LOAD

      @1 as year

      @2 as month

      @3 as product

       

      FROM [myfile.csv]

       

      store * from [myfile] WHERE year = 2017 into [lib://test/mynewfile1.txt]

      store * from [myfile] WHERE year = 2016 into [lib://test/mynewfile2.txt]

      ....

       

      This obviously does not work, just an example. Is it possible to do something like this?

       

       

      /Anton

        • Re: Load one file and store it in multiple files
          Martin Pohl

          while storing, you can't reduce datas, only fields.

          but you can load datas from your source in loops

          example:

          for vCount = 0 to 1   //how many loops

          let vYear = (year(today()) - $(vCount));  //start Loop with "today"

          myfile:

          LOAD

          @1 as year

          @2 as month

          @3 as product

           

          FROM [myfile.csv]

          where @1 = $(vYear);                              //Limit load datas

           

          store * from [myfile] into [lib://test/mynewfile_$(vYear).txt]     //store with year in filename

           

          drop table myfile;   //clear ram

          next                         //next loop

           

           

           

          regards

          • Re: Load one file and store it in multiple files
            Bill Markham

            For each file you wish to store, first create an internal table and them store it.  Maybe something this :

             

            Temp :

            Load

            @1 as year

            @2 as month

            @3 as product

            From [myfile.csv]  // probably needs a lib: connection

             

            2017 :

            NoConcatenate

            Load

            *

            resident Temp

            where Year = 2017 ;

             

            Store Temp into [lib://test/mynewfile2017.txt]  (txt); ;

            Drop Table 2017 ;

             

            2016 :

            NoConcatenate

            Load

            *

            resident Temp

            where Year = 2016 ;

             

            Store Temp into [lib://test/mynewfile2016.txt]  (txt); ;

            Drop Table 2016 ;


            Drop Table Temp ;


            [script above not tested so may have typo's]

            • Re: Load one file and store it in multiple files
              Tamil arasu

              Hi Anton,

               

              If you want to store the data year wise, you can use below script.

               

              Data:
              LOAD Year,
              Month,
              Product
              FROM
              [myfile.csv];

              Temp_Year:
              Load Distinct Year
              Resident Data;

              FOR i=1 to NoOfRows('Temp_Year')
              LET vYear = FieldValue('Year',$(i));

              NoConcatenate
              Temp_Store:
              Load *
              Resident Data Where Year = '$(vYear)';


              STORE Temp_Store into [lib://test/$(vYear).txt];

              DROP Table Temp_Store;

              NEXT;

              DROP Tables Data, Temp_Year;


              If you want to store the data year and month wise, you can try below script.


              Data:
              LOAD Year,
              Month,
              Product,

              Year & Month as YearMonth
              FROM
              [myfile.csv];

              Temp_YearMonth:
              Load Distinct YearMonth
              Resident Data;

              FOR i=1 to NoOfRows('Temp_YearMonth')
              LET vYearMonth = FieldValue('YearMonth',$(i));

              NoConcatenate
              Temp_Store:
              Load *
              Resident Data Where YearMonth = '$(vYearMonth)';


              DROP Field YearMonth From Temp_Store;

              STORE Temp_Store into [lib://test/$(vYearMonth).txt];

              DROP Table Temp_Store;
              NEXT;

              DROP Tables Data, Temp_YearMonth;


              Good luck.

              • Re: Load one file and store it in multiple files
                Anton Wallmark

                Thank you guys, all of them actually worked. Considering I have a huge amount of data and this was just a simple example, Tamil's answer resulted in the least amount of code needed to perform the task.

                 

                THANKS!