Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Techies,
I hope you will be able to help me in below requirement.
I have a table1 containing a Word field and table2 containing Word Descriotion field
if the text field value matches with any sub string value of Word Description , then i have to bring columns from table1 into table2.
Also there is no common column existing in both the tables.
Below is the sample data for both the tables.
Table1:
Word | Classification | Decision |
Access | Operate | Operate |
missing data | Technical | Avoid |
Access Rights | Operate | Consider |
Application - Script | Operate | Consider |
Bad Data | Technical | Avoid |
Bad Config | Operate | Consider |
user error | Operate | Consider |
user error | Knowledge | Consider |
missing data | Technical | Avoid |
Server | Infra | |
Systems | Infra | |
Connectivity issue | Infra | Consider |
Connectivity issue | Infra |
Table2:
Word Descriotion | IncidentNo |
Access related Issue | 2345 |
File has Missing Data | 2346 |
User facing Access Rights issue | 2347 |
File is identified with Bad Data | 2348 |
user error identified | 2349 |
Need to rectify user error ASAP | 2350 |
missing data needs to be filled | 2351 |
Server related Issue | 2352 |
Systems integration issue | 2353 |
Connectivity issue from client machine | 2354 |
Expected Output table:
Word Descriotion | IncidentNo | Word | Classification | Decision |
Access related Issue | 2345 | Access | Operate | Operate |
File has Missing Data | 2346 | missing data | Technical | Avoid |
User facing Access Rights issue | 2347 | Access Rights | Operate | Consider |
File is identified with Bad Data | 2348 | Bad Data | Technical | Avoid |
user error identified | 2349 | user error | Operate | Consider |
user error identified | 2349 | user error | Knowledge | Consider |
Need to rectify user error ASAP | 2350 | user error | Operate | Consider |
Need to rectify user error ASAP | 2350 | user error | Knowledge | Consider |
missing data needs to be filled | 2351 | missing data | Technical | Avoid |
Server related Issue | 2352 | Server | Infra | |
Systems integration issue | 2353 | Systems | Infra | |
Connectivity issue from client machine | 2354 | Connectivity issue | Infra | Consider |
Connectivity issue from client machine | 2354 | Connectivity issue | Infra |
Please let me know the solution if anyone able to find it.
Thanks in Advance,
Sekhar.
Try this
t1:
LOAD * INLINE [
Word, Classification, Decision
Access, Operate, Operate
missing data, Technical, Avoid
Access Rights, Operate, Consider
Application - Script, Operate, Consider
Bad Data, Technical, Avoid
Bad Config, Operate, Consider
user error, Operate, Consider
user error, Knowledge, Consider
missing data, Technical, Avoid
Server, Infra,
Systems, Infra,
Connectivity issue, Infra, Consider
Connectivity issue, Infra,
];
t1Mapping:
Mapping
LOAD Lower(' ' & Word & ' '),
'/' & Word & '\'
Resident t1;
t2:
LOAD *,
TextBetween(MapSubString('t1Mapping', ' ' & lower([Word Description]) & ' '), '/', '\') as Word;
LOAD * INLINE [
Word Description, IncidentNo
Access related Issue, 2345
File has Missing Data, 2346
User facing Access Rights issue, 2347
File is identified with Bad Data, 2348
user error identified, 2349
Need to rectify user error ASAP, 2350
missing data needs to be filled, 2351
Server related Issue, 2352
Systemsintegration issue, 2353
Connectivity issue from client machine, 2354
];
Left Join (t2)
LOAD *
Resident t1;
DROP Table t1;
Try this
t1:
LOAD * INLINE [
Word, Classification, Decision
Access, Operate, Operate
missing data, Technical, Avoid
Access Rights, Operate, Consider
Application - Script, Operate, Consider
Bad Data, Technical, Avoid
Bad Config, Operate, Consider
user error, Operate, Consider
user error, Knowledge, Consider
missing data, Technical, Avoid
Server, Infra,
Systems , Infra,
Connectivity issue, Infra, Consider
Connectivity issue, Infra,
];
t1Mapping:
Mapping
LOAD Lower(Word),
'/' & Word & '\'
Resident t1;
t2:
LOAD *,
TextBetween(MapSubString('t1Mapping', lower([Word Description])), '/', '\') as Word;
LOAD * INLINE [
Word Description, IncidentNo
Access related Issue, 2345
File has Missing Data, 2346
User facing Access Rights issue, 2347
File is identified with Bad Data, 2348
user error identified, 2349
Need to rectify user error ASAP, 2350
missing data needs to be filled, 2351
Server related Issue, 2352
Systems integration issue, 2353
Connectivity issue from client machine, 2354
];
Left Join (t2)
LOAD *
Resident t1;
DROP Table t1;
I add a small modification to the Sunny Talwar script to consider the existence of more than one distinct value present in [Word] inside [Word Description].
Note: i modified the description of incident number 2345 to illustrate the point.
SET NullInterpret = '-';
t1:
LOAD DISTINCT * INLINE [
Word, Classification, Decision
Access, Operate, Operate
missing data, Technical, Avoid
Access Rights, Operate, Consider
Application - Script, Operate, Consider
Bad Data, Technical, Avoid
Bad Config, Operate, Consider
user error, Operate, Consider
user error, Knowledge, Consider
missing data, Technical, Avoid
Server, Infra, -
Systems, Infra, -
Connectivity issue, Infra, Consider
Connectivity issue, Infra, -
];
t1Mapping:
MAPPING LOAD Lower(Word), '/' & Word & '\|' RESIDENT t1;
t2:
LOAD *, IncidentNo & '_' & Word AS KeyTemp
WHERE Not Exists(IncidentNo) Or (Not Exists(KeyTemp, IncidentNo & '_' & Word) And Len(Trim(Word)))
;
LOAD *,
TextBetween(SubField(MapSubString('t1Mapping', Lower([Word Description])), '|'), '/', '\') AS Word
;
LOAD * INLINE [
Word Description, IncidentNo
Access related Issue and Bad Data, 2345
File has Missing Data, 2346
User facing Access Rights issue, 2347
File is identified with Bad Data, 2348
user error identified, 2349
Need to rectify user error ASAP, 2350
missing data needs to be filled, 2351
Server related Issue, 2352
Systems integration issue, 2353
Connectivity issue from client machine, 2354
];
LEFT JOIN (t2)
LOAD * RESIDENT t1;
DROP TABLE t1;
DROP FIELD KeyTemp;
Thanks for your prompt reply Sunny,
I have got one more issue here.
If the word is "systems" and the description is like "systemsIntegration" which is not having any gap, then the script should not match the word and Description.
Can you pls suggest, how can I resolve this?
Thanks,
Sekhar
Try this
t1:
LOAD * INLINE [
Word, Classification, Decision
Access, Operate, Operate
missing data, Technical, Avoid
Access Rights, Operate, Consider
Application - Script, Operate, Consider
Bad Data, Technical, Avoid
Bad Config, Operate, Consider
user error, Operate, Consider
user error, Knowledge, Consider
missing data, Technical, Avoid
Server, Infra,
Systems, Infra,
Connectivity issue, Infra, Consider
Connectivity issue, Infra,
];
t1Mapping:
Mapping
LOAD Lower(' ' & Word & ' '),
'/' & Word & '\'
Resident t1;
t2:
LOAD *,
TextBetween(MapSubString('t1Mapping', ' ' & lower([Word Description]) & ' '), '/', '\') as Word;
LOAD * INLINE [
Word Description, IncidentNo
Access related Issue, 2345
File has Missing Data, 2346
User facing Access Rights issue, 2347
File is identified with Bad Data, 2348
user error identified, 2349
Need to rectify user error ASAP, 2350
missing data needs to be filled, 2351
Server related Issue, 2352
Systemsintegration issue, 2353
Connectivity issue from client machine, 2354
];
Left Join (t2)
LOAD *
Resident t1;
DROP Table t1;
Sekhar, did Sunny's last post get you what you needed? If so, do not forget to come back and use the Accept as Solution button on that post to mark it for others and to give Sunny credit. If you are still working on things, let us know where things stand, so we can see if we can get you further help.
Regards,
Brett
Thankyouverymuch Sunny, its working as expected.