Matching Fields from Different Sources

[artempotsuray]

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!

[swuehl]

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]

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]

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]

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]

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

[swuehl]

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

[hamza99a]

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]

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?