4 Replies Latest reply: Feb 4, 2014 1:58 PM by Gysbert Wassenaar RSS

    load script

    Mambi Badi

      hi everyone,

      from the table below

      category 1
      sub category 1.1
      sub category 1.2
      sub category 1.3
      category 2
      sub category 2.1
      sub category 2.2

      i want to load my data so i get this results :

       

      category 1sub category 1.1
      category 1sub category 1.2
      category 1sub category 1.3
      category 2sub category 2.1
      category 2sub category 2.2

       

      thanks

        • Re: load script
          Nicole Smith

          Load script like the following should do the trick (example file also attached):

          DataTemp:

          LOAD * INLINE [

              Category

              category 1

              sub category 1.1

              sub category 1.2

              sub category 1.3

              category 2

              sub category 2.1

              sub category 2.2

          ];

           

          Data:

          LOAD Category,

              SubField(Category, 'category ', 2) as CategoryNum

          RESIDENT DataTemp

          WHERE NOT Category like 'sub*';

          LEFT JOIN (Data)

          LOAD Category as [Sub Category],

              SubField(SubField(Category, 'sub category ', 2), '.', 1) as CategoryNum

          RESIDENT DataTemp

          WHERE Category like 'sub*';

           

          DROP TABLE DataTemp;

          DROP FIELD CategoryNum;

          • Re: load script
            Gysbert Wassenaar
            Temp:
            LOAD *
              , if(left(F1,3)='sub',peek(Category),F1) as Category
              , if(left(F1,3)='sub',F1) as SubCategory
            INLINE [
                F1
                category 1
                sub category 1.1
                sub category 1.2
                sub category 1.3
                category 2
                sub category 2.1
                sub category 2.2
            ];
            
            Result:
            LOAD Category, SubCategory
            RESIDENT Temp
            where len(trim(SubCategory));
            
            DROP TABLE Temp;