Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sekharQV
Creator
Creator

Compare fields between two tables without having a common column

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:

WordClassificationDecision
AccessOperateOperate
missing dataTechnicalAvoid
Access RightsOperateConsider
Application - ScriptOperateConsider
Bad DataTechnicalAvoid
Bad ConfigOperateConsider
user errorOperateConsider
user errorKnowledgeConsider
missing dataTechnicalAvoid
ServerInfra 
Systems Infra 
Connectivity issueInfraConsider
Connectivity issueInfra 

 

 

Table2:

Word DescriotionIncidentNo
Access related Issue2345
File has Missing Data2346
User facing Access Rights issue2347
File is identified with Bad Data2348
user error identified2349
Need to rectify user error ASAP2350
missing data needs to be filled2351
Server related Issue2352
Systems integration issue2353
Connectivity issue from client machine2354

 

 

Expected Output table:

Word DescriotionIncidentNoWordClassificationDecision
Access related Issue2345AccessOperateOperate
File has Missing Data2346missing dataTechnicalAvoid
User facing Access Rights issue2347Access RightsOperateConsider
File is identified with Bad Data2348Bad DataTechnicalAvoid
user error identified2349user errorOperateConsider
user error identified2349user errorKnowledgeConsider
Need to rectify user error ASAP2350user errorOperateConsider
Need to rectify user error ASAP2350user errorKnowledgeConsider
missing data needs to be filled2351missing dataTechnicalAvoid
Server related Issue2352ServerInfra 
Systems integration issue2353Systems Infra 
Connectivity issue from client machine2354Connectivity issueInfraConsider
Connectivity issue from client machine2354Connectivity issueInfra 

 

Please let me know the solution if anyone able to find it.

Thanks in Advance,

Sekhar.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
sunny_talwar

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;
JGMDataAnalysis
Creator III
Creator III

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;

 

clipboard_image_0.png

 

 

sekharQV
Creator
Creator
Author

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

sunny_talwar

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;
Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
sekharQV
Creator
Creator
Author

Thankyouverymuch Sunny, its working as expected.