I am trying to combine some legacy Excel data with SQL data. This app is about pieces shipped to customers, quantities into monthly buckets.

I can give the SQL side I have an input parameter @partNbr. I have one year of SQL data. This works ok.

The Excel data is a ten years of data.

The column headings of SQL and Excel correspond.

I need guidance on... Can this be done? Use a HOLD fild? Do the icon colors matter (blue and yellow) in the JOIN screen? Should I BLEND?

Help would be appreciated.

This message has been edited. Last edited by: Tamra,

When you say combine, are you talking about concatenation of the data in the RDBMS and EXCEL into one data set or are you trying to JOIN these two tables? Either way, this is possible. For the former, you can combine the data using TABLE and MORE commands. For the latter, I'd load the Excel file into a DBMS (or at least FOCUS with an INDEX) and JOIN them. Of course all of this assumes you can edit the code in Text Editor. I don't think you can do it in the GUI.

Bill,

Recently, I had to join data from an excel spreadsheet to a "master" file create from a SQL sproc. I did that by using the upload functionality in InfoAssist, and then joining the data sources on a key field available in both files. All without having to use the text editor. Hope this helps some. Thanks

Luiz/Babak:

1. Thanks for writing.
2. How do I JOIN?
3. Inside InfoAssist, I can get the Join GUI open.
4. I start with the SPROC table... that has a parameter search for @partNumber... on the left.
5. The excel data... with ALL the partNumbers... is on the right.
6. My JOIN arrow is going from left to right.
7. I do not have a parameter for the Excel data... 8. Are these two tables JOIN_able?
9. How do I see the result of my JOIN?
10. I am using a hosted version of InfoAssist... via Plex ERP.
11. How do I get to the TABLE and MORE commands?
12. Is there a way for me to get to FOCUS or the text editor?

The auto-JOIN only happens if there are columns in both tables with the same name and format. If this isn't the case, you have to click and hold the mouse button on the column name of the table to the left and drag it over the column name of the field to the right and then drop it. Then you'll have a line drawn from the left table to the right. Right click over the line to change the join properties or edit the join to make it unique or left outer, et cetera.

To edit the code and actually type the JOIN, you can create a simple focus executable, save then right click on the name of this fex and choose Edit with Text Editor. Just remember, InfoAssist GUI can parse and understand a subset of the FOCUS language. Consequently, if you type something that's not included in the GUI and save the code, IA will not be able to open it in the GUI from that point on and you will only be able to edit it in code. I'm not familiar with the hosted version, so I don't know if they've exposed the Text Editor to users.

1. I uploaded an Excel sheet with the same columns... including an index column.
2. In the JOIN screen...
3. The columns are key(CC) partNumber(PN) month(MM) year(YR) monthsAgo(MAGO) pieces(PCS).
4. The column names for both the SPROC table and the Excel are identical.
5. In the IA top left resources panel, all of these column names appear twice... with yellow boxes.
6. I have one year of data in the SPROC, ten years of data in the upldaded Excel.
7. I can get all the data to show in the report but... that I have all of the columns there twice.
8. Sum... PCS PCS... By MM MM... Across YR YR... the results are too jumbled.
9. The data types of the columns are not the same like PN PN... I6, I9... Where can I adjust these?
10. That the columns are there twice tells me the JOIN is incorrect.

Bill,

Welcome to Focal Point !

Using InfoAssist column selection is how the columns are added to the report. Are you adding the columns twice - I'm trying to understand how columns appear twice on the report.

Step 9 - to change the field format there are multiple ways
- using a define/compute the field can be renamed and a new format set up
- in the master file description the "FORMAT=" can be changed but caution must be taken
with changes made directly to the master file description
- the field added into the InfoAssist report can be changed using the "Format" option in the tool bar

Here is a link to Join Group - Join and Blend - there might be some information that will help you out here.

10) If the 2 files contain the same columns names then they will appear twice in the Data Panel on the left.
Select either columns and see if you can verify the data. If you hover over the fieldname a tooltip will pop up to indicate the file(segment) that the field is coming from.

Thank you for participating in the Focal Point Forum.

Kind Regards,
Tamra Colangelo
IBI Focal Point Moderato

This message has been edited. Last edited by: Tamra,

Tamra:

1. Thanks for writing.
2. As I am still a forum newbie, I did not see your post until today.
3. As I need to APPEND data, that is why the columns appear twice twice.
4. I have looked at JOIN and BLEND but... right now... I cannot see how to make it append.

--Bill