Forum Discussion

aspirationalbec's avatar
aspirationalbec
Frequent Visitor
2 years ago
Solved

Nested lookupvalue

I have a table of assets that range from a room, a building, a barrack, group of barracks, site. 

 

There is one field [mch_code] that uniquely identifies that item for each and a [sup_mch_code] that identifies and links the next level up in the hierarchy. 

 

The table looks like this:

 

MCH_CODESUP_MCH_CODEOBJ_LEVELOPERATIONAL_STATUS
OUR WILTON SITELAR GROUPBARRACKSIN_OPERATION
NV 0 080AIRFIELD_CMP_WESTBUILDINGSCRAPPED
BX B 017BULFORDBUILDINGIN_OPERATION
BX B 018BULFORDBUILDINGIN_OPERATION
BX B 019BULFORDBUILDINGIN_OPERATION
BX B 020BULFORDBUILDINGIN_OPERATION
BX B 021BULFORDBUILDINGIN_OPERATION
BX B 021-001BX B 021ROOM IN_OPERATION
BX B 021-002BX B 021ROOM IN_OPERATION
BX B 021-003BX B 021ROOM IN_OPERATION

 

 

All levels of the hierarchy are in this table, the [obj_level] detemines what level the row is at.

 

I'm using DQ but am managing to use LOOKUPVALUE as a calculated column. 

 

I'm trying to link together a room to a barrack which is two levels.

 

The building can be found via using the room's [sup_mch_code] but I need to then use the building's [sup_mch_code] to find the barracks. 

 

At the moment, I have:

bs_mch_bar =
var _roomsup = IF(EQUIPMENT__ID[OBJ_LEVEL]="ROOM",EQUIPMENT__ID[SUP_MCH_CODE])
RETURN
IF(AND(EQUIPMENT__ID[OBJ_LEVEL]="ROOM",EQUIPMENT__ID[SUP_MCH_CODE]=EQUIPMENT__ID[bs_mch_building]),LOOKUPVALUE(EQUIPMENT__ID[SUP_MCH_CODE],EQUIPMENT__ID[MCH_CODE],_roomsup),
IF(OR(EQUIPMENT__ID[OBJ_LEVEL]="BUILDING",EQUIPMENT__ID[OBJ_LEVEL]="INFRASTRUCTURE"),EQUIPMENT__ID[SUP_MCH_CODE],
IF(EQUIPMENT__ID[OBJ_LEVEL]="BARRACKS",EQUIPMENT__ID[MCH_CODE],
IF(EQUIPMENT__ID[OBJ_LEVEL]="GROUP OF BARRACKS","GRPBAR",
IF(EQUIPMENT__ID[OBJ_LEVEL]="SITE","SITE",
"23")))))
 
But now I'm getting an error message saying that LOOKUPVALUE isn't applicable in DQ. What is an alternative formula that I can use please? BTW, the GRPBAR, SITE & 23 fields are just there to error check!
Need Help
  • aspirationalbec's avatar
    aspirationalbec
    2 years ago

    Managed to work it out using a bit of SQL at source.

     

    Posting here in case it helps anyone else:

     

     

    select
    eo.mch_code code,
    eo.operational_status code_operational_status,

    eo1.mch_code room,
    eo1.operational_status room_operational_status,

    eo2.mch_code building,
    eo2.operational_status building_operational_status,

    eo3.mch_code barracks,
    eo3.operational_status barracks_operational_status,

    eo4.mch_code grpbar,
    eo4.operational_status grpbar_operational_status,

    eo5.mch_code site,
    eo5.operational_status site_operational_status from equipment_object eo

    join equipment_object eo1 on eo.SUP_MCH_CODE = eo1.mch_code
    join equipment_object eo2 on eo1.SUP_MCH_CODE = eo2.mch_code
    join equipment_object eo3 on eo2.SUP_mch_code = eo3.MCH_CODE
    join equipment_object eo4 on eo3.SUP_mch_code = eo4.MCH_CODE
    join equipment_object eo5 on eo4.SUP_mch_code = eo5.MCH_CODE

4 Replies

    • aspirationalbec's avatar
      aspirationalbec
      Frequent Visitor
      2 years ago

      Hi Greg_Deckler , edited the original post but here you are: 4

      MCH_CODESUP_MCH_CODEOBJ_LEVELOPERATIONAL_STATUS
      OUR WILTON SITELAR GROUPBARRACKSIN_OPERATION
      NV 0 080AIRFIELD_CMP_WESTBUILDINGSCRAPPED
      BX B 017BULFORDBUILDINGIN_OPERATION
      BX B 018BULFORDBUILDINGIN_OPERATION
      BX B 019BULFORDBUILDINGIN_OPERATION
      BX B 020BULFORDBUILDINGIN_OPERATION
      BX B 021BULFORDBUILDINGIN_OPERATION
      BX B 021-001BX B 021ROOM IN_OPERATION
      BX B 021-002BX B 021ROOM IN_OPERATION
      BX B 021-003BX B 021ROOM IN_OPERATION
      • aspirationalbec's avatar
        aspirationalbec
        Frequent Visitor
        2 years ago

        Managed to work it out using a bit of SQL at source.

         

        Posting here in case it helps anyone else:

         

         

        select
        eo.mch_code code,
        eo.operational_status code_operational_status,

        eo1.mch_code room,
        eo1.operational_status room_operational_status,

        eo2.mch_code building,
        eo2.operational_status building_operational_status,

        eo3.mch_code barracks,
        eo3.operational_status barracks_operational_status,

        eo4.mch_code grpbar,
        eo4.operational_status grpbar_operational_status,

        eo5.mch_code site,
        eo5.operational_status site_operational_status from equipment_object eo

        join equipment_object eo1 on eo.SUP_MCH_CODE = eo1.mch_code
        join equipment_object eo2 on eo1.SUP_MCH_CODE = eo2.mch_code
        join equipment_object eo3 on eo2.SUP_mch_code = eo3.MCH_CODE
        join equipment_object eo4 on eo3.SUP_mch_code = eo4.MCH_CODE
        join equipment_object eo5 on eo4.SUP_mch_code = eo5.MCH_CODE