Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
artempotsuray
Contributor III
Contributor III

Matching Fields from Different Sources

Hello everyone,

I have two files that have similar data. Source 1 is a excel file and source 2 is a sql server. I need to match a couple of fields from each file but the issue is not all of data is exact. For example:

source 1 will have the field "Equipment_Name" containing "A7 STARTER" while source 2 will have the field CURRENT_MACHINE containing "A-7". 

The first issue is because the fields are from different sources, I cannot reference the field names in the load data editor when loading the data:

LOAD

    wildmatch('*'&Equipment_Name&'*', '*'&CURRENT_MACHINE&&'*') as "MACHINE_NAME",

    Alternate_Name,

    Brass_Tag,

    "1881 Number",

    Value_Stream

FROM [lib://G Drive/Value_Stream.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

The error occurs at CURRENT_MACHINE because it is not loaded from that specific source.

Also I tried joining the 2 sources together using JOIN and I made sure that the Field Names were identical for qlik to recognize it but it wasn't joining.

Any help is appreciated,

Thanks in advance!

15 Replies
swuehl
MVP
MVP

Seems like you need to clean your data, for example like shown in

Data Cleansing

Another issue would be how to connect your two tables, you probably need to rename the key fields to common names, then just keep the tables linked, join the tables or concatenate them. This decision will be part of your data modelling.

andreas_koehler
Creator II
Creator II

Hallo Stefan,

bitte verbinde Dich mit mir für eine Kontaktaufnahme via private messaging für eine Dienstleistung.

--

Apologies for hi-jacking this tech. discussion but getting in contact is rather difficult.

----

Andreas

artempotsuray
Contributor III
Contributor III
Author

Stefan Thank you for the response. My only problem with trying to clean up the data using mapping is that I have a large data file, 20000+ rows and creating a mapped table with all possible entries will be very difficult. Is there any other way that qlik could do it by finding similarities?

Also I tried joining the tables by having the same name for a field name but qlik wasn't executing it. In source 1 I have a field named CURRENT_MACHINE. In source 2 I made my matching filed name like this:

JOIN

LOAD

     Equipment_Name as CURRENT_MACHINE,

     ...,

FROM ...

and although this has worked in the past for me it qlik was not joining the 2 fields together

hamza99a
Creator II
Creator II

I would try maybe a left join based on the same field name. You can rename a field in the Load script to match the other one by simply saying

Load

CURRENT_MACHINE as EQuipment_NAme

artempotsuray
Contributor III
Contributor III
Author

I tried this but qlik isn't recognizing it that the field names are the same.

swuehl
MVP
MVP

Maybe the problem ist rather that no key values are matching. Do you have any matching values at all?

hamza99a
Creator II
Creator II

maybe you can try editing this in the data model viewer and see if any keys are being made. This helped me find connections that i could never see before. Make sure no synthetic keys are made as that creates a pretty big problem

balabhaskarqlik

may be, refer this to compare data from tables and create different data.

Compare 2 table

Modify your script pertains matching data.

artempotsuray
Contributor III
Contributor III
Author

My data is always changing so I reload the data periodically. Am I able to reload in data model viewer or does it create a permanent data table?