Error while generating days between start date and End date

[#1]

Hi Guru's,

i have script like this

LET Start = num(makedate(2008,1,1));

let End=num((Asof);

LET NumOfDays = End - Start + 1;

Date_src:

LOAD

  $(Start) + RowNo() - 1 AS DateNum

  ,Date($(Start) + RowNo() - 1) AS Date

AUTOGENERATE 1 

WHILE $(Start)+IterNo()-1<= $(End); 

start date is 2008 Manually entered.

End date is coming from excel data .

iam geeting error as attached

please find the sample application

How to solve this

any help would be greately appreciated

thanks in advance

[sunny_talwar]

raj babu wrote:

Hi Guru's,

i have script like this

LET Start = num(makedate(2008,1,1));

let End=num((Asof);

LET NumOfDays = End - Start + 1;

Date_src:

LOAD

  $(Start) + RowNo() - 1 AS DateNum

  ,Date($(Start) + RowNo() - 1) AS Date

AUTOGENERATE 1

WHILE $(Start)+IterNo()-1<= $(End);

End seems to have an extra parenthesis. Where is Asof coming from here? another table?? You might need to use Peek function here

[#3]

End is coming from excel file its another table.

peek () how to use in this scenario

Asof Field Contains only one Date Column.

Please find Attached

[sunny_talwar]

Does columne Asof contain only one value?? If it does than you can try this may be:

LET End=Peek('Asof');

[#5]

No luck Sunny.

Thanks for your Time

[sunny_talwar]

What about this:

LET End=Num(Peek('Asof'));

[#7]

Tried but no luck

its not recognizing End date, while reloading its throwing erorr

[sunny_talwar]

Can you post the script for the table where you are bringing in Asof field from the excel file?

[#9]

Hi here is the script

head:

LOAD

if(not match ([Job Posting Title],'The data contained in this report is as of 06/03/2015 01:46 PM EDT'),[Job Posting Title]) as [Job Posting Title],

if(match([Job Posting Title],'The data contained in this report is as of 06/03/2015 01:46 PM EDT'), [Job Posting Title] )as Test,

     [Worker: Worker Supervisor],

     Worker,

     [Worker ID],

     Status,

     [Start Date],

     [End Date],

     "Current Bill Rate [ST/Hr]",

     [Physical Location of Worker],

     [Worker Closed Date],

     [Cumulative Committed Spend],

     [Total Spend]

FROM

(biff, embedded labels, header is 1 lines, table is Headcount$);

Res:

load *,

Mid(if(wildmatch(Test,'**/**/**') ,Test),44,10) as Asof

resident head;

drop table head;

[sunny_talwar]

Seems like your Asof is not read as a date.

Try this:

head:

LOAD

// Mid(if(wildmatch(if(not match ([Job Posting Title],'The data contained in this report is as of 06/03/2015 01:46 PM EDT'),[Job Posting Title]),'**/**/**') ,[Total Spend]),7,10) as Asof

if(not match ([Job Posting Title],'The data contained in this report is as of 06/03/2015 01:46 PM EDT'),[Job Posting Title]) as [Job Posting Title],

if(match([Job Posting Title],'The data contained in this report is as of 06/03/2015 01:46 PM EDT'), [Job Posting Title] )as Test,

    [Worker: Worker Supervisor],

    Worker,

    [Worker ID],

    Status,

    [Start Date],

    [End Date],

    "Current Bill Rate [ST/Hr]",

    [Physical Location of Worker],

    [Worker Closed Date],

    [Cumulative Committed Spend],

    [Total Spend]

    // Mid(if(wildmatch([Total Spend],'**/**/**') ,[Total Spend]),7,10) as Asof

FROM

(biff, embedded labels, header is 1 lines, table is Headcount$);

Res:

load *,

Date#(Mid(if(wildmatch(Test,'**/**/**') ,Test),44,10), 'MM/DD/YYYY') as Asof //ASSUMING MM/DD/YYYY FORMAT

resident head;

drop table head;

and then the variable:

LET End=Num(Peek('Asof'));