Concatenating Multiple rows into one

[IainP]

Am brand new to this forum and this product, but here goes.

I have a data export from a supplier that I need to use in a database, but in its current format this is making it very tricky.

The issue is that the csv file has 10 columns of text. The first four are in the correct format (for example make, model, engine size, trim), however the problem is that the next field can have upto 11 varients (for eample options: aircon/power steering/abs etc etc) based on the preceeding four columns and as such this is creating duplicate rows for each additional varient.

What I would like to do is where make/model/engine & trim are the same create only one row, with an additional 11 columns (option 1 to Option 11)which will be populated in order) and then to continue with the remaining 5 columns of original text from the supplier.

I must appologise in advance that this is all new to me, so I may come back and ask some rather silly questions.

Thanks

Iain

[Søren Dyhr]

See if you can make sense of this template:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000686

...or ask to it?

--sd

[IainP]

Soren,

Thank you for your response. This looks almost like what I need. However what I am trying to achieve is all this information in a table that then can be exported as a csv file. If this info was to be exported, the contact names do not go into a seperate field.

I have attached an example of how the data is coming to me and a table of how I need to convert it to read.

Is this possible?

example_db.zip

Edited January 8, 2009 by Guest

[Søren Dyhr]

It's not clear here what you wish, do you need to lump together data or do you wish to export including null values ... if it's the later take a look at the changes I made to your file.

--sd

example_db.zip

[IainP]

What I am looking at achieving is for the input data (shown in the input data file) to be converted into the format shown in the export data file.

So reducing the number of duplicated rows of data, as well as having the two columns (i - shelf heights and ii - Notch Heights), collated within the rows. Where there are only 4 or 7 or whatever number for each record type then I would expect to see that number of blank cells (for shelf heights and Notch Heights).

I opened the database on your link and could not see any differences, are you able to point me in the correct direction?

Many Thanks

Iain

[Fenton]

First, let's start off by saying that neither the Import nor the Export are really proper relational structure. The "relationally correct" structure would store the Shelf and Notch data in a separate table. The method I used for the Export would also work with such a table, if the calculations were pointed there.

What I used was a script to create the records for the unique combo of data (as I saw it). I used [x] Allow creation of related records, using a unique serial ID field as the field to set. It could be another field.

The multiple Shelf and Notch fields (which is really poor relational structure, but such is often the nature with exports to the "real" world) were "populated"* dynamically via unstored calculations using GetNthRecord ( relationship::field; 1), GetNthRecord ( relationship::field; 2), etc.. I only did the calculation for a few of them; too tedious -]

In other words, reading the data from the other table where each is a separate record. This would also work if they were a separate table.

Don't know what's up with the Date and Text fields however, as they would get "flattened" by the export.

[P.S. You can delete all the records in the Export table, then run the Create Export script from the Scripts menu. It works using the found set in the Import table's layout; which would be what you'd have immediately after an import.]

* I quoted "populated" because this data is not IN the Export table; it is still in the Import table (or would be in its own table with correct relational structure). So you could not delete it from its source table.

example_fej.fp7.zip

Edited January 8, 2009 by Guest

[IainP]

This example looks like it does the job perfectly. If I was to expect a copy of this import data once a week and as such I would like to save each weeks collated data into a seperate table is there an automated way that I can do this, maybe by just adding a date suffix for each new table.

Thanks

Iain

Edited January 8, 2009 by Guest

[Fenton]

I don't really know what you mean by "save each weeks collated data". The trouble is, you are using FileMaker to turn one kind of poorly structured data into another form of poorly structured data. So it's kind of hard for us "purists" to advise you, since neither structure is really correct.

If it was me, I would take the Import, populate the Export table with the "common" data, effectively summarizing it, then populate a 3rd table with the "Shelf and H" data from the Import, repointing the GetNthRecord calculations to the "Shelf & H" table, then delete the Import data. That would be the permanent storage.

Or don't do the above, and just leave the Import as is -]

Also, as I said, I don't know about the Text and Date fields. Which in this import are all the same. But that doesn't answer the question about where it should go in a proper structure.

When you say "collated data" per week, are you talking about adding up something? Remember, I don't know what this stuff is.

Edited January 8, 2009 by Guest

[IainP]

I will do as you suggest and split these tables.

The data itself will be sent to me on a weekly basis. Some of the details within the file will change weekly and I need to try and identify what the differences are from last weeks to this weeks file. However I think I have a workable solution to this, that I will dig into tomorrow.

Thank you for your help with this, you will never appreciate how much this has helped me in trying to get a solution for a currently very manual weekly process.

[Fenton]

Here is the same file, more or less, with an addition to the script to create records in a sub-table of export. This is the correct relational structure for the data. Whether it is useful is another story; but it usually is -]

I did not delete the Import records, but you could do that (instead of going to the next record), after the data has been moved; though I still don't know where Date and Text belong (Export or Shelves).

example2_fej.fp7.zip

[IainP]

I have tried to mimic your example database (not the latest one with split tables), to try and get it working with the actual live data.

It has thrown up a number of challenges, the largest of which is that my version only seams to add one new row to the export file. Can you see why this may be?

There are a number of issues that it has also thrown up, which I am going to have to find a solution for.

Thanks

Iain

New_Automated_PB.zip

[Fenton]

The problem is that the If step in the script has its calculation /*commented out*/, which renders it useless. You need to remove the comment characters, then retarget any fields if necessary.* Then it will work.

The 3rd table needs some changes, and addition to the script also; I'll do that later. The above is the main problem.

*Or change the name of the table occurrence on the graph to match; that's easier. Your names are kind of long.

Edited January 9, 2009 by Guest

[IainP]

I have now removed the errors within the if statement and this script now works.

I now have two further things that need to be done before this data is in a format in which I can use it.

1st: I need to add a field next to 'Block Start' which needs to be called 'Block No'. This needs to return a value of 1 to n. If you look in the output table Baby Food, plan 230 has three rows, where the block starts are 0, 1.2 & 2.4. In the 'Block No' field I would need to see the lowest number in the sequence as 1, then the next as 2,etc etc until all variants for that plan have been numbered.

2nd: I have also noted that in the shelf depth and notch fields the data is being displayed in reverse order. By that I mean that notch zero (0) which on the first line of text is showing as notch height 7 needs to be showing as notch height 1 and then height 2 should be 9 etc etc, rather than the way they are shoing now. Is this possible to reverse?

[Fenton]

I'l start with the 2nd question. Eventually, when dealing with complex flat data, you come to the point where you cannot continue. I think we've come to that point. Because the flat data has multiple values, for multiple fields, it becomes impossible to sort them them separately.

The solution is to put the data into better relational structure, which is almost always possible. In this case, separate records for the Shelf-Notch data. These can also be created during the script, using a similar [x] Allow creation of related records mechanism. It is using the unique serial ID of the "import" table as the key to create, but setting the ID of the "export" table into the table.

The export table uses that to target the records, after the script has run. Now that the shelf and notch records are in their own table, with fields: Shelf, Notch 40mm and Notch 32mm, we can sort them. We do this via new relationships; same key, but sorted differently. These are shown as portals on the Export layout, just to see them.

The fields in Exports, using GetNthRecord calculations, use the appropriate relationship to get the fields in sorted order.

At this point, you could actually delete the records from the Import table, as all the data has been moved, and no longer depends on that table. This would be a good idea if it is used to get new data later. (After testing of course.)

Question 1 was handled by using a self-relationship on the Export table, using all the criteria but NOT the Block Start. You can then Count () that relationship to get the Block No. This can be done either during the script, or with a dynamic calculation in Export. The scripted method has the advantage that it can put the result in a stored field; but the disadvantage that it would not recalculate if you deleted a record, nor added more later that fell into that group (without further action). The dynamic calculation would recalculate, but it is much much slower. They are both on the layout.

P.S. There is likely a faster method to set Block No with the fixed method during the script, using a test on captured variables. But my brain got a little too tangled :-]

Automated_PB_fej.fp7.zip