topic Re: Error while generating days between start date and End date in QlikView

Error while generating days between start date and End date

Wed, 12 Aug 2015 09:49:42 GMT

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

Re: Error while generating days between start date and End date

sunny_talwar — Wed, 12 Aug 2015 09:52:25 GMT

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

Re: Error while generating days between start date and End date

Wed, 12 Aug 2015 10:07:28 GMT

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

Re: Error while generating days between start date and End date

sunny_talwar — Wed, 12 Aug 2015 10:10:16 GMT

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

LET End=Peek('Asof');

Re: Error while generating days between start date and End date

Wed, 12 Aug 2015 10:18:19 GMT

No luck Sunny.

Thanks for your Time

Re: Error while generating days between start date and End date

sunny_talwar — Wed, 12 Aug 2015 10:21:49 GMT

What about this:

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

Re: Error while generating days between start date and End date

Wed, 12 Aug 2015 10:23:32 GMT

Tried but no luck

its not recognizing End date, while reloading its throwing erorr

Re: Error while generating days between start date and End date

sunny_talwar — Wed, 12 Aug 2015 10:25:03 GMT

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

Re: Error while generating days between start date and End date

Wed, 12 Aug 2015 10:26:14 GMT

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;

Re: Error while generating days between start date and End date

sunny_talwar — Wed, 12 Aug 2015 10:30:04 GMT

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

Re: Error while generating days between start date and End date

Anonymous — Wed, 12 Aug 2015 10:30:34 GMT

Hi Raj,

Please Maybe try as below:

LET End=num(Date#(Asof));

Regards

Neetha

Re: Error while generating days between start date and End date

Wed, 12 Aug 2015 10:42:20 GMT

Hi Sunny,Neetha ,

No luck

thanks for your time

actually that Asof Field is coming as a field value of Job posting Title Field

here is the asource Excel file

Re: Error while generating days between start date and End date

MarcoWedel — Wed, 12 Aug 2015 10:57:15 GMT

Hi,

another solution could be:

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2008,1,1) as MinDate, 
    Max(Asof) as MaxDate 
Resident Res;

hope this helps

regards

Marco

Re: Error while generating days between start date and End date

Wed, 12 Aug 2015 12:14:24 GMT

Thanks all