1 Reply Latest reply: Apr 4, 2012 10:46 AM by Dennis Hoogenboom RSS

    How to relate data extracted from an Excel source - once seperated?

      With the script below I'm seperating data from four fields in an excel document using a WHERE clause. Does this mean that the records related to the LA LOAD scripts Year, Month, Day and Hour fields will be replaced with those records fro the CPU LOAD script?

       

      Is there a way for me to use a sub-query to load data using one LOAD statement.

       

      CPU:
       Load
            HOST_SERVER as Server,
            LOGICAL_NAME as [CPU-Utilization],
            TIME_STAMP as [CPU-Utilization TimeStamp],
             Year(TIME_STAMP) as Year,
             Month(TIME_STAMP) as Month,
             Day(TIME_STAMP) as Day,
             Hour(TIME_STAMP) & ':00 - ' & (Hour(Date(TIME_STAMP,'hh:mm'))+1) & ':00' as Hour,
            MEAS_VALUE as [CPU-Utilization Value]
       FROM
       [C:\Documents and Settings\Administrator\Desktop\Renstan's Tasks\NIS (New)\NIS_SERVERS.xls]
       (biff, embedded labels)
       where left (LOGICAL_NAME,4)='CPU-';
      
      LA:
       Load
            HOST_SERVER as Server,
            LOGICAL_NAME as [Load Average],
            TIME_STAMP as [Load Average TimeStamp],
             Year(TIME_STAMP) as Year,
             Month(TIME_STAMP) as Month,
             Day(TIME_STAMP) as Day,
             Hour(TIME_STAMP) & ':00 - ' & (Hour(Date(TIME_STAMP,'hh:mm'))+1) & ':00' as Hour,
            MEAS_VALUE as [Load Average Value]
       FROM
       [C:\Documents and Settings\Administrator\Desktop\Renstan's Tasks\NIS (New)\NIS_SERVERS.xls]
       (biff, embedded labels)
       where left (LOGICAL_NAME,4)='Load';
      
        • How to relate data extracted from an Excel source - once seperated?
          Dennis Hoogenboom

          Your script doesn't make sence to me.

          It creates synthetic tables am I right?

           

          Why not just load it something like:

           

          Load

                HOST_SERVER as Server,

                IF(LEFT(LOGICAL_NAME,4)='CPU-' , LOGICAL_NAME) as [CPU-Utilization],

                IF(LEFT(LOGICAL_NAME,4)='Load' , LOGICAL_NAME) as [Load Average],

                TIME_STAMP,

                 Year(TIME_STAMP) as Year,

                 Month(TIME_STAMP) as Month,

                 Day(TIME_STAMP) as Day,

                 Hour(TIME_STAMP) & ':00 - ' & (Hour(Date(TIME_STAMP,'hh:mm'))+1) & ':00' as Hour,

                IF(LEFT(LOGICAL_NAME,4)='CPU-', MEAS_VALUE) as [CPU-Utilization Value],

                IF(LEFT(LOGICAL_NAME,4)='Load', MEAS_VALUE) as [Load Average Value]

          FROM

          [C:\Documents and Settings\Administrator\Desktop\Renstan's Tasks\NIS (New)\NIS_SERVERS.xls]

          (biff, embedded labels)

           

          Let me know if this is usefull ok?

           

          Good luck,

          Dennis.