Replacing data during load

[#1]

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'));

[swuehl]

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

[#3]

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.

[swuehl · Accepted solution]

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'));


[#5]

That worked flawlessly, and now I get how it works! Thank you.