topic Re: Matching Fields from Different Sources in Connectivity & Data Prep

Matching Fields from Different Sources

artempotsuray — Thu, 23 Aug 2018 19:07:07 GMT

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!

Re: Matching Fields from Different Sources

swuehl — Thu, 23 Aug 2018 19:35:35 GMT

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.

Re: Matching Fields from Different Sources

andreas_koehler — Fri, 24 Aug 2018 08:35:58 GMT

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

Re: Matching Fields from Different Sources

artempotsuray — Fri, 24 Aug 2018 11:54:05 GMT

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

Re: Matching Fields from Different Sources

hamza99a — Fri, 24 Aug 2018 12:46:53 GMT

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

Re: Matching Fields from Different Sources

artempotsuray — Fri, 24 Aug 2018 13:03:30 GMT

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

Re: Matching Fields from Different Sources

swuehl — Fri, 24 Aug 2018 13:21:30 GMT

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

Re: Matching Fields from Different Sources

hamza99a — Fri, 24 Aug 2018 13:36:06 GMT

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

Re: Matching Fields from Different Sources

BalaBhaskar_Qlik — Fri, 24 Aug 2018 13:50:47 GMT

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

Compare 2 table

Modify your script pertains matching data.

Re: Matching Fields from Different Sources

artempotsuray — Fri, 24 Aug 2018 13:58:05 GMT

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?

Re: Matching Fields from Different Sources

hamza99a — Fri, 24 Aug 2018 14:04:03 GMT

the data model viewer essentially shows you just how your data is linked and which dimensions it is using to link those tables. Reloading data every day has no effect on this. If you could just go to your data model viewer and screenshot it, I feel that I may be able to help you as it will help me better understand the connections you are trying to make.

Re: Matching Fields from Different Sources

artempotsuray — Fri, 24 Aug 2018 14:14:54 GMT

I am using JOIN and WHERE clauses in my script causing my data model viewer to show one big data table, can i break it down to show all the smaller tables that im putting together.

Re: Matching Fields from Different Sources

hamza99a — Fri, 24 Aug 2018 14:21:30 GMT

if you want, you can share a qvf file with me and I can take a look. Even if it has sensitive data some mock up data should do.

Re: Matching Fields from Different Sources

artempotsuray — Fri, 24 Aug 2018 14:28:54 GMT

I have data coming from a sql server so you wouldnt be able to load it in any way so im not sure if that is possible

Re: Matching Fields from Different Sources

hamza99a — Fri, 24 Aug 2018 14:36:13 GMT

if you could even just take like a couple rows from excel and a couple rows from sql and put them both in an excel it would help, The reason being is although SQL and excel are different at the end of the day data is data.

Re: Matching Fields from Different Sources

swuehl — Sat, 25 Aug 2018 16:50:49 GMT

If you want to check the problems with your JOIN, you can remove the JOIN prefix from your LOAD statement, but rename the key fields to a common name, hence keep the tables linked in your model.

A filter pane of the common key fields should now show you all symbols of both tables. you should also be able to create a table with three dimensions, your key field and fields from either table (and showing field values for all records). This should show you where your key values are matching and where not.

You can create the necessary fields like this, too:

CheckKeys:

LOAD

Equipment_Name as "MACHINE_NAME",

'Equipment' as Table

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

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

CONCATENATE (CheckKeys)

LOAD

CURRENT_MACHINE as "MACHINE_NAME",

'SQL' as Table;

SQL SELECT

CURRENT_MACHINE

FROM YourSQLTable;

Could you load this table, create a chart as described and export this table and post it?