Adding a column with a repeating value

[#1]

I am loading a table from a website.  The site has the same table of data for each of several years (a table for 2013, a table for 2012,  a table for 2011, etc.).  Unfortuanely, only the table title has the year.  There is no column for it.  When I load the table is there a way to add the year as a dimension in the table for each record?  If so, how?

[MarcoWedel]

can you post the url of this site?

[#3]

2013 - Bridges by Wearing Surface - NBI - Inspection and Evaluation - Bridge - Structures - Federal ...

[#4]

I also have a follow up question.  This page has several tables and when I try to load it directly from the site it has several tables that get listed (they show up as @1, @2, @3, etc.).  If I save it as an excel file it lists all of them as well in one file though.  The only way to clean it up is to delete all the rows that have info from the other tables.  I feel like I am missing an easier way of loading just the one table I need.  Any thoughts?

[sebastiandperei]

Use Tablename() Function, in another field. It returns the name of the table you are reading:

Load

     Field1,

     Field2,

     Tablename()     as Year

From ....

[#6]

Thanks! I will try that!

Alex

Sent from my iPhone

[MarcoWedel]

Hi Alexander,

I found that the year is also coded in the URL of this site,

so one solution could be:

FOR vYear = 0 to 13
  LET vYearForm = num(vYear,'00');
  CrossTable([Type of Wearing Surface], [Number of Bridges], 2)
  LOAD 2000+$(vYear) as Year,
      *
  FROM
  [https://www.fhwa.dot.gov/bridge/nbi/no10/wearing$(vYearForm).cfm]
  (html, codepage is 1252, embedded labels, table is [Count by Type of Wearing Surface])
  WHERE(State <> 'TOTALS');
NEXT

hope this helps

regards

Marco

[#8]

Marco,

I think I understand this, but am curious as how to do this if I am

transforming the data for each table? That os to say, I need to remove the

"TOTALS" row from each one.

[MarcoWedel]

Hi,

each TOTALS row will be removed because the WHERE clause applies to all the loaded tables during the loop.

A combination of the different facts (e.g. number and area of bridges) could be done like this:

FOR vYear = 0 to 13
  LET vYearForm = num(vYear,'00');
  tabTypeofWearingSurface:
  CrossTable([Type of Wearing Surface], [Number of Bridges], 2)
  LOAD 2000+$(vYear) as Year,
      *
  FROM
  [https://www.fhwa.dot.gov/bridge/nbi/no10/wearing$(vYearForm).cfm]
  (html, codepage is 1252, embedded labels, table is @1)
  WHERE(State <> 'TOTALS');
NEXT

FOR vYear = 0 to 13
  LET vYearForm = num(vYear,'00');
  tabTemp:
  CrossTable([Type of Wearing Surface], [Area (Sq Meters)], 2)
  LOAD 2000+$(vYear) as Year,
      *
  FROM
  [https://www.fhwa.dot.gov/bridge/nbi/no10/wearing$(vYearForm).cfm]
  (html, codepage is 1252, embedded labels, table is @4)
  WHERE(State <> 'TOTALS');
NEXT

Join (tabTypeofWearingSurface)
LOAD * Resident tabTemp;

DROP Table tabTemp;

regards

Marco

[MarcoWedel]

Please close this thread if your problem is solved.

thanks

regards

Marco