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.
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.
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.
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.
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?
