LOOKUPVALUE Error Message

lcallah93
lcallah93 Member, Infor Partner Posts: 8
in Birst

I am receiving an 'Index 0 out of bounds for length 0' when using a double lookup. I am guessing there is some data missing in the lookup keys, but I am not sure how to account for that (?).

image.png

LOOKUPVALUE(0,LOOKUPVALUE(0,[WFM-EmployeeBalanceLog.GHR Employee ID],1,SELECT [Work Assignment.Employee Key],[Work Assignment.HR Organization Unit Key] FROM [ALL]),1,SELECT [HR Organization Unit Chart.HR Organization Unit Key],[HR Organization Unit Chart.HR Organization Unit Key] FROM [ALL]) WHERE [WFM-EmployeeBalanceLog.GHR Employee ID] IS NOT NULL

Please and thank you,

Lisa

Best Answer

  • lcallah93
    lcallah93 Member, Infor Partner Posts: 8
    Answer ✓
    https://community.developer.infor.com/discussion/comment/656#Comment_656

    It is - that was my attempt at error handling. I finally got it though - I had to use IFNULL -

    LOOKUPVALUE(0,LOOKUPVALUE(0,[WFM-EmployeeBalanceLog.GHR Employee Key],1,SELECT [Work Assignment.Employee Key],[Work Assignment.HR Organization Unit Key] FROM [ALL]),1,SELECT IFNULL([HR Organization Unit Chart.HR Organization Unit Key],'1/0000'),[HR Organization Unit Chart.HR Organization Unit Description Level 3] FROM [ALL])

Answers

  • Diane Mcelhiney
    Diane Mcelhiney Member, Staff Posts: 11
    edited November 21

    Is the WHERE clause supposed to be included as part of the SELECT of the outer LOOKUPVALUE? Just remove the WHERE. It just appears as though it is unnecessary.

    LOOKUPVALUE(0,LOOKUPVALUE(0,[WFM-EmployeeBalanceLog.GHR Employee ID],1,SELECT [Work Assignment.Employee Key],[Work Assignment.HR Organization Unit Key] FROM [ALL]),1,SELECT [HR Organization Unit Chart.HR Organization Unit Key],[HR Organization Unit Chart.HR Organization Unit Key] FROM [ALL])

Categories