topic Re: Adding a column with a repeating value in QlikView

Adding a column with a repeating value

Fri, 11 Apr 2014 22:19:39 GMT

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?

Re: Adding a column with a repeating value

MarcoWedel — Fri, 11 Apr 2014 22:23:49 GMT

can you post the url of this site?

Re: Adding a column with a repeating value

Fri, 11 Apr 2014 22:27:28 GMT

2013 - Bridges by Wearing Surface - NBI - Inspection and Evaluation - Bridge - Structures - Federal Highway Administrati…

Re: Adding a column with a repeating value

Fri, 11 Apr 2014 22:31:16 GMT

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?

Re: Adding a column with a repeating value

sebastiandperei — Sat, 12 Apr 2014 02:33:29 GMT

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

Load

Field1,

Field2,

Tablename() as Year

From ....

Re: Adding a column with a repeating value

Sat, 12 Apr 2014 03:16:30 GMT

Thanks! I will try that!

Alex

Sent from my iPhone

Re: Re: Adding a column with a repeating value

MarcoWedel — Sun, 13 Apr 2014 23:47:10 GMT

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

Re: Adding a column with a repeating value

Mon, 14 Apr 2014 15:16:06 GMT

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.

Re: Re: Adding a column with a repeating value

MarcoWedel — Tue, 15 Apr 2014 01:02:59 GMT

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

Re: Adding a column with a repeating value

MarcoWedel — Thu, 24 Apr 2014 08:04:09 GMT

Please close this thread if your problem is solved.

thanks

regards

Marco