Issue with the sub

[fawazqutami]

Dears,

I'm trying to load the below script but I've got a message 'Field 'VST_ID' not found'!

why is that? please help ..................

Thank you

Sub TPP_ROOT (VST_ID)

VST_SUMM:

LOAD

    Sum(SPAYER) as GUEST,

    Sum(CSHARE) as PAYER

    FROM C_REV where IH_EMR_VST_ID_MAS=VST_ID Group By IH_EMR_VST_ID_MAS;

Store VST_ID,PAYER, GUEST from VST_SUMM into TPP_SUM;

Drop Table VST_SUMM;

EndSub

Sub TPP_VST (VST_ID))

VST_SUMM:

LOAD

    Sum(SPAYER) as GUEST,

    Sum(CSHARE) as PAYER

    FROM C_REV

(qvd) where IH_EMR_VST_ID=VST_ID Group By IH_EMR_VST_ID;

Store VST_ID,PAYER, GUEST from VST_SUMM into TPP_SUM;

Drop Table VST_SUMM;

EndSub

//------------------------------------------------

OPV: LOAD*FROM OPV_Table

For A=1 to NoOfRows('OPV')

if NOTE_TYPE=0 then

Call TPP_ROOT(IH_EMR_VST_ID);

elseif NOTE_TYPE<>0 then

Call TPP_VST(IH_EMR_VST_ID);

Next A

[rwunderlich]

Both of your STORE statements reference the the field "VST_ID". However, VST_ID is not created in the LOAD statement.  It may be simpler if you did not reference any fields in your STORE statement. e.g.

STORE VST_SUMM into TPP_SUM;

Also, it's conventional to use the .qvd extension on the STORE filename. e.g.

STORE VST_SUMM into TPP_SUM.qvd;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[tomasz_tru]

Try to use: $(VST_ID) instead of VST_ID when referencing parameter in subroutine.

Tomasz

[fawazqutami]

I used the qualified path but still the same error ... !

[fawazqutami]

Still the same error "Error in expression: ')' expected:'"

[fawazqutami]

[tomasz_tru]

Can we see the whole script as it is now?

[fawazqutami]

I believe that the issue is solved. but am not getting any records in TPP_SUM

[dwforest]

You're not getting the ID in your loop.

Why not just join?

OPV: LOAD*FROM [lib://Stage2 (uranus_qlikadmin)/OPV_Table.qvd](qvd);

LEFT JOIN

VST_SUMM:

LOAD

    IH_EMR_VST_ID as vstID,

    Sum( SELF_PAYER ) as GUEST,

    Sum( COMPANY_SHARE ) as PAYER

    FROM [lib://Stage2 (uranus_qlikadmin)/C_REV.qvd](qvd)

    ;

Even if your loop did work, the resulting file would only contain the last ID as each would overwrite the previous.