Forum Discussion

gaiusgw's avatar
gaiusgw
Icon for Helper III rankHelper III
4 years ago
Solved

Pull data from unrelated table like VLOOKUP

I have 2 tables that have a control # in common. I need to bring in the PO # into the other table based on the common control #.    Tables; OPENHEAD which has the control number and the PO number....
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    I think it may be the blank control nos in the TRANS table which are causing problems. Try

    PO # = 
var controlNumber = TRANS[control no]
return IF( NOT ISBLANK(controlNumber),
 SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, OPENHEAD, OPENHEAD[ID]), 
   TREATAS( { controlNumber}, OPENHEAD[control no]) ),
   "@val", OPENHEAD[PO #]
) )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

the column needs to go on the TRANS table. I thought that was where you wanted it ?

gaiusgw's avatar
gaiusgw
Icon for Helper III rankHelper III
4 years ago

You are a legend! Thanks so much for your patience on this one. I was trying to put the new calculation under an area where I have lots of other independent calculations and that was the problem like you said. Added under TRANS and good to go! Thanks again!