I have 2 tables. One has a large list of servers and the other has a subset of this. (all of the subset lies in the large list) The subset is a list of servers where some are being monitored and some are not. We would like to create a field which shows servers which are monitored and those which are not which apsna across both tables. However, when I try to do so we get the wrong figures.
Table1:
LOAD
CATALOGUEINFRASTRUCTURE.CatalogueID
CATALOGUEINFRASTRUCTURE.InfrastructureId
CATALOGUEINFRASTRUCTURE.EnvironmentID
CATALOGUEINFRASTRUCTURE.ServerTypeID ,CATALOGUEINFRASTRUCTURE.InfrastructureAliasId as InfraStructureAliasId, CATALOGUEINFRASTRUCTURE.CatalogueInfrastructureDesc as CatalogueInfrastructureDesc from ......qvd;as CatalogueId, as InfrastructureId, as EnvironmentId, as ServerTypeId TABLE2:
LOAD
hostname environment "report_time" "agent_type" "agent_version" if
table1 is the large list while table 2 is the subset. Therefore all of the hostnames in table 2 are contained within table1. We would like to add a field to table1 which would have 'monitoring installed' or 'monitoring not installed'. I have tried to concatenate but this simply adds on all of the subset which distorts the figures.
If you could shed some light on a possible solution to this it would be greatly appreciated.
Thanks
(match(agent_type, 'NT', 'LZ', 'UX') <> 0, 'Base', 'Extended') as AgentCategory; as AgentVersion,as AgentType,as ReportTime,as TivoliEnvironment, "instance_name" as Instance, as Hostname,
Tried attaching QVW but seemed to have timout issues. Attahced the two table scripts if iit helps.
We would like to have a dimension which shows all the servers monitored and those that aren't. We want to add and overwrite the data it but when we concatenate it on, it doesn't seem to do so, it just adds the data on. Any help on this would be much appreciated.
Again, the code seems truncated. You have a LOAD at the end with no source data. As for the left join itself, you shouldn't have a colon at the end of that line. I'm surprised that isn't giving you an error. And while it shouldn't make any difference, I often put the join key, InfrastructureId in this case, as the first field on both tables I'm joining. I think it just makes it more clear for maintenance as you don't have to search for what the tables are joining on.
This should hopefully be the correct script. I had just changed some names for security purposes and put the colon in by mistake. The select statement should have also been at the bottom.
