3 Replies Latest reply: Feb 23, 2017 3:02 PM by Kaushik Solanki RSS

    Changing value in column on the load script

    Sara Vasquez

      I have the following load

      LOAD

          Subject,

          "Override Flag",

          num(Grade) as Grade,

          "School Performance School Flag",

           "Attributed District Name"

       

      FROM [my file]

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

      WHERE MATCH ("Subject",'M', 'R','E' ,'S')

      and ("Attributed District Name"='PHILADELPHIA CITY SD')

      and ("School Performance School Flag"='Y')

      and ("Grade"<'11')

      ;

       

      I want to change the value of the Subject so that instead of M I get math and instead of R I get reading. Any help is appreciated it. I am thinking of using an if statement, but I am not sure where I should put it on the Load script. Thanks for any help provided.

        • Re: Changing value in column on the load script
          Massimo Grossi

          LOAD

              Subject,

              if(Subject = 'M', 'math',

              if(Subject = 'R', 'reading',

              Subject
              )) as Subject,

              "Override Flag",

              num(Grade) as Grade,

              "School Performance School Flag",

               "Attributed District Name"

          FROM [my file]

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

          WHERE MATCH ("Subject",'M', 'R','E' ,'S')

          and ("Attributed District Name"='PHILADELPHIA CITY SD')

          and ("School Performance School Flag"='Y')

          and ("Grade"<'11')

          ;

          • Re: Changing value in column on the load script
            Bill Markham

            Using if's then maybe something like this

             

            LOAD

                if ( Subject = 'M', 'math' ,

                if ( Subject = 'R' , 'reading' , Subject ) ) as Subject ,

                "Override Flag",

                num(Grade) as Grade,

                "School Performance School Flag",

                "Attributed District Name"

            ..................


            • Re: Changing value in column on the load script
              Kaushik Solanki

              Hi,

               

              You can try if statement as below.

               

              LOAD

                  Subject,

              If(Subject='R','Reading,

                   if(Subject='M','XYZ')) as NewSubject,

                  "Override Flag",

                  num(Grade) as Grade,

                  "School Performance School Flag",

                   "Attributed District Name"

               

              FROM [my file]

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

              WHERE MATCH ("Subject",'M', 'R','E' ,'S')

              and ("Attributed District Name"='PHILADELPHIA CITY SD')

              and ("School Performance School Flag"='Y')

              and ("Grade"<'11')

              ;

               

               

              Or you can use the Apply Map as Below.

               

              Map:

              Load * inline [

              X,Y

              R,Reading

              M,XYZ

              ];

               

               

              LOAD

                  Subject,

              Applymap('Map',Subject) as NewSubject,

                  "Override Flag",

                  num(Grade) as Grade,

                  "School Performance School Flag",

                   "Attributed District Name"

               

              FROM [my file]

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

              WHERE MATCH ("Subject",'M', 'R','E' ,'S')

              and ("Attributed District Name"='PHILADELPHIA CITY SD')

              and ("School Performance School Flag"='Y')

              and ("Grade"<'11')

              ;

               

               

              Regards,

              Kaushik Solanki