Using value from previously loaded table to filter subsequent query

[#1]

Hello.  I am trying to use  OBJECTID, and   CHANGENR as filters for the next query.  In debug mode, both vCHANGENR  and vOBJID are returning nulls.  My script is below.  I can't figure out why I am getting nulls.

CDHDR:

LOAD

    OBJECTCLAS,

    OBJECTID,

    CHANGENR,

    USERNAME,

    UDATE,

    TCODE,

    CHANGE_IND

FROM [lib://QVD_Store/CDHDR.qvd]

(qvd)

WHERE WildMatch(OBJECTID,'00003*');

LOAD

CONCAT(Chr(39)&OBJECTID&Chr(39),',') as OBJID

RESIDENT CDHDR;

LET vOBJID = OBJID;

LET vCHANGENR = CHANGENRID;

CDPOS:

LIB CONNECT TO 'SAP';

INNER JOIN LOAD

OBJECTCLAS,

OBJECTID,

CHANGENR,

TABNAME,

TABKEY,

FNAME,

CHNGIND,

VALUE_NEW,

VALUE_OLD;

SELECT 

OBJECTCLAS,

OBJECTID,

CHANGENR,

TABNAME,

TABKEY,

FNAME,

CHNGIND,

VALUE_NEW,

VALUE_OLD

FROM CDPOS

WHERE OBJECTCLAS = 'VERKBELEG' AND TABNAME = 'VBEP' AND FNAME = 'EDATU' AND OBJECTID IN('$(vOBJID)');

[felipedl · Accepted solution]

Hi Sam,

For you to get the values for the variables:

LET vOBJID = OBJID;

LET vCHANGENR = CHANGENRID;

The above statement will retunr null, because neither OBJID and CHNGENRID are defined.

You must use a peek('FieldName',rowNumber,'TableName') funtion.

Something like:

ObjectID:

LOAD

CONCAT(Chr(39)&OBJECTID&Chr(39),',') as OBJID

RESIDENT CDHDR;

LET vOBJID = peek('OBJID',0,'ObjectID');

LET vCHANGENR = peek('CHANGENR',0,'CDHDR');

this will get some values to your variables

The bolded zero in the peek formula is the number of the row in the data you have, so if you have more than 1 row in your loadeed table, i'll have to figure which one you need.

Felipe.

[#3]

Hi Felipe,

This worked!

Thanks,

Sam

[felipedl]

Glad it helped Sam , could you mark the anwser as correct please?

Felipe.