why lookup doesnt work?

[#1]

Hello! Could anyone tell me please? what am i doing wrong, why Lookup doesnt return any value?//it's not the final 'good ' app, just cant go on without woking well lookup

Temp:

LOAD Period, //date of transaction

  MonthName(Period) as Month,

  DayName(Period) as Day,

  Year(Period) as Year,

  Date(Period) as Date,

  Month(Period)&'-'&Year(Period) as MonthYear,

     cash_flow, //DEBIT OR CREDIT

     partner, //partner name

     transaction_$, //TRANSACTION IN USD

        if(cash_flow='debit',transaction_$,-1*transaction_$) as    DebtorChange

FROM

(ooxml, embedded labels, table is debts);

Result:

NoConcatenate

LOAD

     Period,  Day,

     Month,

     Year,

     Date,

     MonthYear,

     cash_flow,

     partner,

     transaction_$,

     DebtorChange,

    RecNo() as  sTRING_NUM

Resident Temp  Order by partner asc, Period asc;

Drop Table Temp;

left join load

    Date,

    0 as flag,

    Date  as Date_start,

    MonthYear,

    cash_flow,

    partner,

    transaction_$,

    DebtorChange,

   if(partner=previous(partner),numsum( DebtorChange, peek( 'Debtor_sum')),DebtorChange ) as Debtor_sum, // sum of debit debts of the partner on this current date

   if( cash_flow='debit',transaction_$,0) as debit,

   if( cash_flow='debit',Date,0) as debit_date_start,

   if( cash_flow='credit',transaction_$,0) as credit

resident Result Order by partner asc, Period asc;

res:

load

    Date,

    flag,

    Date_start,

    partner,

    DebtorChange,

    debit,

   debit_date_start,

   credit,

   recno() as numstring,

if(partner=previous(partner),if(debit>0,(lookup('credit',0,'flag')-debit),0)) as debit_sum,

if(partner=previous(partner),if(credit>0,(lookup('debit',0,'flag')-debit),0)) as credit_sum

// if(rangesum(above(Sum(DebtorChange),0,recno()))=>0,1,0) as flag_repayed

resident Result Order by partner asc, Period asc;

drop table Result;

THANKS IN ADVANCE

[Anonymous]

Remove the quotes for the field flag and check.

Lookup('credit',0,flag)

[tresesco · Accepted solution]

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

The second argument is supposed to be a field name (that you are passing as '0'), and third argument might not require quotes.

[jagan]

Hi,

Check the syntax of Lookup() from Qlikview help file,

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

I am not sure what you are trying to do with Lookup?  Are you looking for Peek()? Can you explain with an explain about what you are trying to do?

Regards,

Jagan.

[#5]

Well, as I mentioned, it's just a start of a bigger app..

Client has a huge amount of partners,  lots of transactions. He wants to see debts of partners, dew to their age: wich debts are less then month, which 1-3 months, which 3-6 months... (the best- to be able to choose the limits in the app by himself....) He want to see it in different lines or colours of bars..

the payment scheme is FIFO: the new transfer goes to repay the oldest debt... So there are ... emm......steps...=)

I've tried to make different loops, but didn't succeed...i guess it best to do the calculations in the script, because there are few thousands of partners..