Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to convert date of invoice to number of day ?

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

1 Solution

Accepted Solutions
trdandamudi
Master II
Master II

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);

View solution in original post

10 Replies
paulyeo11
Master
Master
Author

my qvw

sunny_talwar

Try this:

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

paulyeo11
Master
Master
Author

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
Master II
Master II

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
Master
Master
Author

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
Master II
Master II

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

Thanks

paulyeo11
Master
Master
Author

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
Master II
Master II

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:

DaysConversionToYearAndMonth.jpg

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