topic Re: Compare fields between two tables without having a common column in QlikView

Compare fields between two tables without having a common column

PRS — Sat, 16 Nov 2024 19:52:31 GMT

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.

Re: Compare fields between two tables without having a common column

sunny_talwar — Thu, 17 Oct 2019 19:05:16 GMT

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;

Re: Compare fields between two tables without having a common column

JGMDataAnalysis — Thu, 17 Oct 2019 22:18:56 GMT

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;

Re: Compare fields between two tables without having a common column

PRS — Fri, 18 Oct 2019 10:54:11 GMT

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

Re: Compare fields between two tables without having a common column

sunny_talwar — Fri, 18 Oct 2019 10:59:26 GMT

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;

Re: Compare fields between two tables without having a common column

Brett_Bleess — Wed, 23 Oct 2019 19:02:07 GMT

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

Re: Compare fields between two tables without having a common column

PRS — Thu, 24 Oct 2019 07:30:04 GMT

Thankyouverymuch Sunny, its working as expected.