whta's the error?

[#1]

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;

[sivarajs]

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

[#3]

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

[#4]

Thanks Sivaraj, but after i changed it the error is

"field Invoice_Date is not found "

i don't know what's the error?

[#5]

no, i don't know this function

Could you Please clarify it to me?

[#6]

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;

[jonathandienst]

Hi

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

[#8]

Hi Jonathan,

I completely greed ur point.

and Join tables order also incorrect.

thats why, i resend complete script.

--Prabhu Ch

[#9]

Thanks Prabhu, I will try it.

[#10]

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