De-normalizing 1

[harrymossman]

How can I go about de-normalizing a table? I want to take records from a detail table and string them together to put in spreadsheet cells.

The table has a key (integer) related to the master table, a "counties" field, and other fields I don't need.

In the spreadsheet, I want a row for each master record, with the appropriate counties for that record strung together, separated by commas.

It seems like a tc script that somehow loops through records with matching keys, putting them together with string( ) would do the trick. However, I have done only a few simple tc, script, and string( ) codes. So I don't quite know how to start. It's easy to find information about normalizing but not de-normalizing.

Harry

 

[Langley]

I believe I know what you are wanting. Here is a possible way to do it. Let's suppose your tables are called DETAIL (with fields IDNUM and COUNTY) and MASTER (with a key field of IDNUM):

var
  ts         textstream
  tcMas      tCursor
  tcDet      tCursor
  myString   string
  counter    number
endvar

ts.create(":myAlias:SpreadSheet.csv")  
tcMas.open(":myAlias:MASTER.DB")
tcDet.open(":myAlias:DETAIL.DB")
counter = 1
myString = ""

scan tcMas:

   scan tcDet:

      if tcDet.IDNUM = tcMas.IDNUM and counter=2
        then  myString = myString+","+tcDet.COUNTY
      endif
      if tcDet.IDNUM = tcMas.IDNUM and counter=1
        then  myString = tcDet.COUNTY
              counter = 2
      endif

   endscan


 ts.writeLine(myString)
 counter = 1

endscan

I did not test this and it is off the top of my head for the most part, but unless I goofed, this should result in a CSV file that can be imported into any spreadsheet program.

Mac

"Gurth a choth-en-America! Tôl acharn..."


langley_mckelvy@cd4.co.harris.tx.us

 

[harrymossman]

Thanks Mac, that comes pretty close to working.

I found that by changing

ts.create(":myAlias:SpreadSheet.csv")

to

ts.create(":myAlias:SpreadSheet.wb3")

,
I get a QuattroPro spreadsheet, which is what I have to work with.

However, the spreadsheet doesn't have IDNUM and each county is in a separate cell. I could get around the second problem by concatenating within the spreadsheet. That might be better since it would preserve the original data. But I gotta have the id.

Harry

 

[harrymossman]

This works:

var
  ts         textstream
  tcMas      tCursor
  tcDet      tCursor
  myString   string
  counter    number
endvar

ts.create(":myAlias:SpreadSheet.wb3")  
tcMas.open(":myAlias:MASTER.DB")
tcDet.open(":myAlias:DETAIL.DB")
counter = 1
myString = ""

scan tcMas:

   scan tcDet:

      if tcDet.IDNUM = tcMas.IDNUM and counter=2
        then  myString = myString+ "; " +tcDet.COUNTY ;Replaced comma with semicolon

      endif

      if tcDet.IDNUM = tcMas.IDNUM and counter=1
        then  myString = tcMas.IDNUM + "," + tcDet.COUNTY
              counter = 2
      endif

   endscan

 ts.writeLine(myString)
 counter = 1

endscan

 

[Langley]

Then just change:

 if tcDet.IDNUM = tcMas.IDNUM and counter=1
        then  myString = tcDet.COUNTY
              counter = 2
 endif

to:

 if tcDet.IDNUM = tcMas.IDNUM and counter=1
        then  myString = tcMas.IDNUM+","+tcDet.COUNTY
              counter = 2
 endif

If I understand right, that should get you the ID Number in the first cell. Now, I think after reading your post that you originally wanted only two cells, one for the IDNUM and one for the COUNTIES - is that correct?

I'm not a spreadsheet user so I'm not up to speed on import quirks, but you might get away with adding doublequotes around the IDNUM and the group of counties. You would have to change:

 if tcDet.IDNUM = tcMas.IDNUM and counter=1
        then  myString = tcMas.IDNUM+","+tcDet.COUNTY
              counter = 2
 endif

to:

 if tcDet.IDNUM = tcMas.IDNUM and counter=1
        then  myString = "\""+tcMas.IDNUM+"\",\""+tcDet.COUNTY
              counter = 2
 endif

and

add this line:

myString = myString+"\""

right above this line:

ts.writeLine(myString)

I hate those "\" characters and I'm not sure I syntaxed that just right. You'd have to look at the text file to be sure.


Mac

"Gurth a choth-en-America! Tôl acharn..."


langley_mckelvy@cd4.co.harris.tx.us

 

[harrymossman]

Thanks. I'll explore those suggestions. Yes, I wanted to combine the data into 2 fields. I'm not a regular spreadsheet user either.