Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'));
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:
mapsubstring('mapTable', mrASSIGNEES) as
LOAD mrID AS TicketNumber,
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'));
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
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.
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:
mapsubstring('mapTable', mrASSIGNEES) as
LOAD mrID AS TicketNumber,
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'));
That worked flawlessly, and now I get how it works! Thank you.