4 Replies Latest reply: Dec 12, 2011 12:32 PM by bryanxcole RSS

    Replacing data during load

      I'm loading data from a database where the information stored within is stored and called using perl scripts, so it contains code such as __b for a space, __P for (, __p for ), etc. So when I LOAD ASSIGNEES, I get data like:

       

      Support__bTeam__b1

      Support__bTeam__b2

      etc.

       

      I'm not a SQL master, I only know the basics and can normally find answers online but this has me at a loss. I was thinking of incorporating a REPLACE statement, but I don't know how to write it.

       

      Also, some data is loaded that I really don't need.

       

      For instance, using the same example of Teams, I sometimes get a team name and a person's name:

       

      Support__bTeam__b1 Technician__bJohn

       

      or

       

      Support__bTeam__b1 Support__bTeam__b2

       

      or

       

      Support__bTeam__b1 Techniciain__bJohn Support__bTeam__b2

       

      I want to drop all of the technicians completely and I want to be able to call the records by team.

       

      So in my graph, I want to see:

       

      Support Team 1 = 10 records

      Support Team 2 = 12 records

       

      Where some of the records counted could actually have one of the teams, or both.

       

      Does this make any sense?

       

      Here is my load statement:

       

       

      ServiceDeskAssignees:
      LOAD mrID AS TicketNumber,
           mrASSIGNEES As Assignees;
      SQL 
      SELECT *
      FROM Footprints.dbo.MASTER1
      WHERE mrSTATUS!='_DELETED_' 
      AND ((mrREF_TO_MR IS NULL) or ((mrREF_TO_MR NOT LIKE '%R%') and (mrREF_TO_MR NOT LIKE '%Q%'))) 
      AND (mrSTATUS IN ('Open', 'Assigned__bto__bTeam', 'Assigned__bto__bAgent', 'Pending', 'Pending__bCustomer', 'For__bFollow__bUP', 'Customer__bResponse', 'Closed'));
      
      
        • Re: Replacing data during load
          Stefan Wühl

          Instead of REPLACE, I would suggest to use mapsubstring function with a mapping table, which also takes care to replace all appropriate placeholders.

           

          To remove the technicians, you could add an appropriate where clause.

           

          All in all, maybe something along these lines:

           

           

          mapTable:

          mapping load * INLINE [

          perl, replace

          __b , " "

          __P , "("

          __p , ")"

          ];

           

          INPUT:

          LOAD * INLINE [

          input

          Support__bTeam__b1 Technician__bJohn

          Support__bTeam__b1 Support__bTeam__b2

          Support__bTeam__b1 Techniciain__bJohn Support__bTeam__b2

          Support__bTeam__b1 __PTechnician__bJohn__p

          ];

           

          RESULT:

          LOAD

          mapsubstring('mapTable', input) as Result

          resident INPUT

          // where not wildmatch(input,'*Technician*')

          ;

           

           

          So I first define a mapping table (just add any other placeholders and the strings to replace). Then I loaded some data (like your table load), and then I did a resident load with where clause to only load the data of interest (to actually see only the limited data, comment the where clause in). One Techniciain is left, because of the typo, but you could adapt the wildmatch to maybe take care of that.

           

          Hope this helps,

          Stefan

            • Replacing data during load

              This didn't work for me, I'm not able to insert the SQL query into the LOAD * INLINE [  ];

               

              What I'm getting is the results showing my SQL query line items. I've never done a mapping table before, so I don't even know why it's failing.

               

              Also, I don't know how I'm going to load another variable outside of the mapping table in order to create a relational link to this new data.

                • Replacing data during load
                  Stefan Wühl

                  No, you don't use an INLINE load for your data, that was just for demonstration.

                   

                  Maybe try this:

                   

                   

                  mapTable:

                  mapping load * INLINE [

                  perl, replace

                  __b , " "

                  __P , "("

                  __p , ")"

                  ];

                   

                  ServiceDeskAssignees:
                  LOAD mrID AS TicketNumber,
                      
                  mapsubstring('mapTable', mrASSIGNEES) as Assignees;
                  SQL
                  SELECT *
                  FROM Footprints.dbo.MASTER1
                  WHERE mrSTATUS!='_DELETED_'
                  AND ((mrREF_TO_MR IS NULL) or ((mrREF_TO_MR NOT LIKE '%R%') and (mrREF_TO_MR NOT LIKE '%Q%')))
                  AND (mrSTATUS IN ('Open', 'Assigned__bto__bTeam', 'Assigned__bto__bAgent', 'Pending', 'Pending__bCustomer', 'For__bFollow__bUP', 'Customer__bResponse', 'Closed'));