topic Re: why lookup doesnt work? in QlikView

why lookup doesnt work?

Thu, 23 Oct 2014 07:20:01 GMT

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

Re: why lookup doesnt work?

Anonymous — Thu, 23 Oct 2014 07:34:21 GMT

Remove the quotes for the field flag and check.

Lookup('credit',0,flag)

Re: why lookup doesnt work?

tresB — Thu, 23 Oct 2014 07:36:23 GMT

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.

Re: why lookup doesnt work?

jagan — Thu, 23 Oct 2014 07:36:51 GMT

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.

Re: why lookup doesnt work?

Thu, 23 Oct 2014 08:05:33 GMT

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..