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

Thu, 29 Mar 2012 08:59:57 GMT

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
 
 (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
 
 (biff, embedded labels)
 where left (LOGICAL_NAME,4)='Load';

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

Anonymous — Wed, 04 Apr 2012 14:46:34 GMT

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

(biff, embedded labels)

Let me know if this is usefull ok?

Good luck,

Dennis.

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

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

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