topic Re: whta's the error? in QlikView

whta's the error?

Tue, 29 Jan 2013 08:20:54 GMT

Hi,

I want to know what's the error in this script?

it said to me "field Inv_Date not found" and takes alot of time in reloading in the step of join colored with red

CASH_RECEIPTS:

LOAD CASH_RECEIPT_ID,

RECEIPT_DATE

from load\Cash_Receipts.qvd(qvd);

Invoice:

LOAD Invoice_ID,

Invoice_Date

from load\Invoice.qvd(qvd);

Receivables:

LOAD CASH_RECEIPT_ID,

Invoice_ID

from load\Receivables.qvd(qvd);

Aging:

LOAD

Rec_Date-Inv_Date as [Days Over],

if(Rec_Date-Inv_Date<=0,'On Time',

if(Rec_Date-Inv_Date>0 and Rec_Date-Inv_Date<31,'1-30',

if(Rec_Date-Inv_Date>30 and Rec_Date-Inv_Date<61,'31-60',

if(Rec_Date-Inv_Date>60 and Rec_Date-Inv_Date<91,'61-90',

if(Rec_Date-Inv_Date>90 and Rec_Date-Inv_Date<121,'91-120',

if(Rec_Date-Inv_Date>120 and Rec_Date-Inv_Date<151,'121-150',

if(Rec_Date-Inv_Date>150,'Over 150'))))))) as Payment_Interval;

LOAD CASH_RECEIPT_ID,

RECEIPT_DATE as Rec_Date

Resident CASH_RECEIPTS;

right join LOAD CASH_RECEIPT_ID,

Invoice_ID

Resident Receivables;

right Join LOAD Invoice_ID,

Invoice_Date as Inv_Date

Resident Invoices;

Re: whta's the error?

sivarajs — Tue, 29 Jan 2013 08:24:14 GMT

you need to use Invoice_Date instead Inv_Date because inside script it wont take the alias name

Re: whta's the error?

Tue, 29 Jan 2013 08:36:21 GMT

And about how you construct the aging intervals, have you heard about the Class() function?

Re: whta's the error?

Tue, 29 Jan 2013 08:44:21 GMT

Thanks Sivaraj, but after i changed it the error is

"field Invoice_Date is not found "

i don't know what's the error?

Re: whta's the error?

Tue, 29 Jan 2013 08:45:16 GMT

no, i don't know this function

Could you Please clarify it to me?

Re: whta's the error?

Tue, 29 Jan 2013 08:57:07 GMT

Hi Mona,

You can use below script.

Directory;

CASH_RECEIPTS:

LOAD CASH_RECEIPT_ID,

RECEIPT_DATE

FROM

Join.xlsx

(ooxml, embedded labels, table is Sheet1);

Directory;

Invoice:

LOAD Invoice_ID,

Invoice_Date

FROM

Join.xlsx

(ooxml, embedded labels, table is Sheet2);

Directory;

Receivables:

LOAD CASH_RECEIPT_ID,

Invoice_ID

FROM

Join.xlsx

(ooxml, embedded labels, table is Sheet3);

LOAD CASH_RECEIPT_ID,

Invoice_ID

Resident Receivables;

right join LOAD CASH_RECEIPT_ID,

RECEIPT_DATE as Rec_Date

Resident CASH_RECEIPTS;

right Join LOAD Invoice_ID,

Invoice_Date AS Inv_Date

Resident Invoice;

Aging:

LOAD

Rec_Date-Inv_Date as [Days Over],

if(Rec_Date-Inv_Date<=0,'On Time',

if(Rec_Date-Inv_Date>0 and Rec_Date-Inv_Date<31,'1-30',

if(Rec_Date-Inv_Date>30 and Rec_Date-Inv_Date<61,'31-60',

if(Rec_Date-Inv_Date>60 and Rec_Date-Inv_Date<91,'61-90',

if(Rec_Date-Inv_Date>90 and Rec_Date-Inv_Date<121,'91-120',

if(Rec_Date-Inv_Date>120 and Rec_Date-Inv_Date<151,'121-150',

if(Rec_Date-Inv_Date>150,'Over 150'))))))) as Payment_Interval;

LOAD CASH_RECEIPT_ID,

Invoice_ID,

Rec_Date,

Inv_Date

Resident Receivables;

Re: whta's the error?

jonathandienst — Tue, 29 Jan 2013 09:02:26 GMT

The last script fragment is trying to resident load from "Invoices", but the table was labelled "Invoice". Therefore Invoice_ID cannot be found as the table "Invoices" does not exist.

Hope that helps

Jonathan

Re: whta's the error?

Tue, 29 Jan 2013 09:16:47 GMT

Hi Jonathan,

I completely greed ur point.

and Join tables order also incorrect.

thats why, i resend complete script.

--Prabhu Ch

Re: whta's the error?

Tue, 29 Jan 2013 09:23:10 GMT

Thanks Prabhu, I will try it.

Re: whta's the error?

Tue, 29 Jan 2013 09:24:54 GMT

no it called invoices it's a syntax error in my writing the discussion, but the original script it's true.

Re: whta's the error?

SunilChauhan — Tue, 29 Jan 2013 09:33:19 GMT

use Invoice inplace of Invoices;

in last line of above code

hope this helps

Re: whta's the error?

Tue, 29 Jan 2013 09:51:57 GMT

but this will make a synthetics table contain this 2 fields (Invoice_ID, Cash_Receipt_Date)

between table Aging and Receivables?

Re: whta's the error?

Tue, 29 Jan 2013 10:02:43 GMT

After loading Aging tableyou can drop Invoice and CASH_RECEIPTS table form document.

--Prabhu Ch

Re: whta's the error?

Tue, 29 Jan 2013 10:22:02 GMT

but there's other fields in this 2 tables i need to use?

Re: whta's the error?

Tue, 29 Jan 2013 10:27:35 GMT

you can load all the other feilds from Invoice AND CASH_RECEIPTS into Receivables table.

check in attachment.....