Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Using value from previously loaded table to filter subsequent query

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)');

1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: Using value from previously loaded table to filter subsequent query

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 Replies
felipedl
Valued Contributor III

Re: Using value from previously loaded table to filter subsequent query

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.

Not applicable

Re: Using value from previously loaded table to filter subsequent query

Hi Felipe,

This worked!

Thanks,

Sam

felipedl
Valued Contributor III

Re: Using value from previously loaded table to filter subsequent query

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

Felipe.

Community Browser