Merge/Join: integer vs text data type problem?

SteveP · May 19, 2010 10:03AM

We are having problems merging two files on a Barcode field. The barcode field is a text field, and appears to be coming in as text in both input files, one containing sales data and the other a product master file.

The sales data file is first passed through a Field Organiser, to rename some fields and to add a calculation. At this stage the Barcode field is showing as Text. Then it is merged with a calendar file, to add some year/month fields. The output from this merge is then merged with the product master file, by matching on barcode. At this point we are getting no matches.

If I insert another Field Organiser block to intercept the output of the sales data/calendar file merge, it shows that the Barcode has become an Integer field. If I try to change the barcode field type to Text, I get the following error message -
Error: ClassCastException: com.visokio.yad cannot be cast to com.visokio.abd

It appears that the sales data/calendar file merge is changing the barcode to an integer. If I start with a brand new datamanager tab and just merge the sales data & product file, it works perfectly.

Any idea what is going on?

Thanks, Steve

SteveP · May 19, 2010 11:01AM

Update - we have installed build 262, and we no longer get the error message when we try to change from Integer to Text. However, the rogue field conversion is still happening.

chris · May 19, 2010 11:34AM

Hi Steve,

Could you install the latest version of Omniscope (265) as there are a number of important fixes related to this problem in this build. All blocks (with the exception of the field organiser) allow you to 'Auto-convert' the input data to the most appropriate type. At present this option is turned on by default. In your merge block, because 'auto convert' is turned on, it will convert the text field to an integer. You can easily turn this option off by opening the block menu and un-checking 'Auto-convert data type'.

In the future we may make it more obvious when 'Auto-convert' is on (perhaps by showing a warning message the first time auto-convert modifies a field). We may also allow you to turn auto convert on or off for all blocks in one go. Let me know what you think.

Regards

Chris

SteveP · May 19, 2010 11:55AM

Hi Chris,

Thanks for the quick response.

I have found the AutoConvert option on the merge blocks, and turned them off. That does the trick, but...

Having saved the file, chnanged the raw data, and re-opened the file, the data refreshes OK. But as soon as I go back to the DataManager tab, I find that the AutoConvert options are turned on again! And maybe because of this, I have to remove the thousand seperator options from some integer fields again too (see my other problem thread)

You mention version 265 - the latest version I can see on the website is 262

Steve

SteveP · May 19, 2010 12:14PM

Update - have found build 265, so I am about to try the same process with that.

SteveP · May 19, 2010 12:22PM

Fell at the first! In 265 I have added a database table (the sales data) and a file (the product master file). When I add a Merge/Join block, it matches them on barcode, says it has found 108000 merged records, but the block stays as "Not Fully Configured". Pressing the Execute button does nothing.

After that I tried again, and did get the merge/join to work. But at the end of it all, having saved the file, and gone back to the DataManager tab, once again the merge/join box is "Not fully configured" and Execute is not working.

chris · May 19, 2010 12:47PM

Hi, would it be possible to send me the files your having problems with? You can scramble fields that contain sensitive data.

Thanks

Chris

SteveP · May 19, 2010 12:52PM

OK - can you let me know your email address?

chris · May 19, 2010 1:07PM

Hi you can post them to this forum.

SteveP · May 20, 2010 12:11PM

Hi Chris,
Thanks for that, but...

I have loaded up the .iok file you sent back, and changed the file sources. Now, as before, the merge/join blocks say they are not fully configured, although they know how many merged records there are etc. The Execute button is showing in red, but clicking it does nothing.

If I delete the first merge block, and replace it with a new one, that works fine, as does the second merge/join block when I replace that. But when I save the file, and re-open it, the second block gives the same "not configured" error.

Not sure what to try next.

Steve

SteveP · May 20, 2010 12:45PM

I think I may have narrowed the problem down.

I started a completely new file, and on the datamanager tab I added the sales data and product master files (the same ones I sent yesterday). Then I joined them on Barcode. And that's all. Saved the file. Closed Omniscope. Opened Omniscope, opened the file, and it all looks OK.

Then I changed the option on the merge/join block to turn off automatic data-type conversion. Saved the file with a new name. Closed & Opened Omniscope, and opened the new version of the file. It comes straight up with "not fully configured" on the merge/join block. If I click on "Test Join" it knows it has the right number of matched records, but the Execute button does nothing.

If I close that version, and go back to the first version (with Autoconvert still on) it is OK.

SteveP · May 21, 2010 10:05AM

All sorted with the latest daily build. Many Thanks

Welcome!

Categories

Ideas Parade

Tagged