I need to check in another table if a record exist if so I need to load a specific value from another table.
I wan't to use following construction but it doesn't work. Can someone help me to find a solution?
IF(ChargeCde<>'""',if(not ISNULL(LOAD * RESIDENT ParmSpVL where Invoice_1.ChargeCde=ParmSpVl.ParmCde and ParmFld='CHARGECDE' and labelCde='CumTrnOv' AND SpecValL='yes'),OrdAmtDLc,0),OrdAmtDLc)as test
If I'm reading your expression right, you could add a field to your ParmSpVL table. It should be the ParmCde, but only established when the conditions above are true. Then, while loading your invoice data, check if the charge code exists on that table in your expression, and finally drop the extra field you added:
[ParmSpVL]: LOAD if( ParmFld ='CHARGECDE' and labelCde='CumTrnOv' and SpecValL='yes',ParmCde) as "Use OrdAmtDLc For This ChargeCde" ... [Invoice_1]: LOAD if(ChargeCde='""' or exists("Use OrdAmtDLc For This ChargeCde",ChargeCde),OrdAmtDLc,0) as test ... ; DROP FIELD "Use OrdAmtDLc For This ChargeCde" ;
