How to convert date of invoice to number of day ?

[paulyeo11]

Hi All

I have a field date_invoice which display when the invoice was issue ? and i like to convert this date by minus today date and give the total number of day due.

May i know how to do it ?

I try :-

date(today) - date_invoice

Not working

Paul

[paulyeo11]

my qvw

[sunny_talwar]

Try this:

Today() - date_invoice as [total number of day due]

[paulyeo11]

Hi Sunny

Thank you very much for your load script , i try to put the load scrip as below , i get error msg , it cannot find the AR_Table. where i go wrong ?

AR_TABLE:

LOAD date_invoice,

  Today() - date_invoice as [total number of day due]

Resident AR_TABLE;

LOAD

  'TDSS' as SOURCE,

  if(mid(@1:9T,2,1)='U',1,0) as US_Cust_R,

  If(Mid(@1:9T,2,1) = 'R','RP',IF(Mid(@1:9T,2,1) = 'U','USD','SGD')) AS US_Cust_AR,

  autonumber( @1:9T & '_' & 'TDSS' ) as ARKey,

  @1:9T as [cust_id],

  left(@10:38T,25) as [company],

[trdandamudi · Accepted solution]

The reason you are getting error because you are in the AR_TABLE and then calling it as Resident AR_TABLE. See if the below will work for you, I added just one line which is highlighted below:

AR_TABLE:

LOAD

//left(FileBaseName(), 4) AS Report5_pay,

  'TDSS' as SOURCE,

  if(mid(@1:9T,2,1)='U',1,0) as US_Cust_R,

  If(Mid(@1:9T,2,1) = 'R','RP',IF(Mid(@1:9T,2,1) = 'U','USD','SGD')) AS US_Cust_AR,

  autonumber( @1:9T & '_' & 'TDSS' ) as ARKey,

  @1:9T as [cust_id],

  left(@10:38T,25) as [company],

  @60:72T as [cur_],

   if(right(@60:72T,1)='-', '-' & left(@60:72T, len(@60:72T)-1),@60:72T) as aging,

   @73:89T as [da3_],

   if(right(@73:90T,1)='-', '-' & left(@73:90T, len(@73:90T)-1),@73:90T) as ar_total,

   @98:110T as [date_invoice],

  Today() - @98:110T as [total number of day due],

  @120:126T as [invoice_no_de]

  //FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\AR_Aging.txt (ansi, fix, no labels, header is 0, record is line);

FROM $(vRAWPath)$(vFile80) (ansi, fix, no labels, header is 0, record is line);

//FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\AP_Aging.txt (ansi, fix, no labels, header is 0, record is line);

[paulyeo11]

Hi Thir

Yes this is what I want . I prefer no using partial reload. Thank you very much.

Paul Yeo

Director

www.tdstech.com<http://www.tdstech.com>

HP+6285883521036 Indo

[trdandamudi]

Good... Happy to hear it is working... Can you please close the thread by marking Sunny's response as correct.

Thanks

[paulyeo11]

Hi Thir

thank you very much . why you ask me mark Sunny answer as correct ?

By the way , Do you know how to display in for example 2100 days display as 5 year 7 month ?

[trdandamudi]

Because Sunny was the first person who responded and gave the answer,

Regarding displaying 2100 days in year and month, please see the attached and hope this helps:

[sunny_talwar]

trdandamudi‌ -

I might have given a quick response which you feel is the correct response, but you took paulyeo11‌ all the way. I think you deserved a correct response more than me. But in any case, Paul, I would highly recommend marking trdandamudi‌'s responses as helpful since those were very informative and will help other's in the future if they land on this page.

Best,

Sunny