topic Re: Error: Table not Found in QlikView

Error: Table not Found

Tue, 28 Jul 2015 10:24:09 GMT

Hi, I am trying to make a master calendar with time.I have loaded an excel file that includes a field with timestamp but when I am trying to load the data, it throws error "Table not Found". Do you have any idea where is the problem?
Thank you

Re: Error: Table not Found

avinashelite — Tue, 28 Jul 2015 11:30:27 GMT

can please share the script ??

Re: Error: Table not Found

sushil353 — Tue, 28 Jul 2015 11:30:50 GMT

Share your script

Re: Error: Table not Found

Tue, 28 Jul 2015 12:35:01 GMT

Dates:

LOAD

Floor(Min(TimeStamp#(mytable, 'MM/DD/YYYY hh:mm:ss'))) AS MinDate,

Floor(Max(TimeStamp#(mytable, 'MM/DD/YYYY hh:mm:ss'))) AS MaxDate

RESIDENT emails;

LET vMinDate = FieldValue('MinDate', 1);

LET vMaxDate = FieldValue('MaxDate', 1);

TempCalendar:

LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS DateNumber,

TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS TempDate

AYTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

Calendar:

LOAD

TempDate AS mytable,

Hour(TempDate) AS CalenderHour,

Minute(TempDate) AS CalenderMinute,

Date( Floor(TempDate)) AS CalendarDate,

Day(TempDate) AS CalendarDayOfMonth,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear,

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

DROP Table Dates;

// I found the code by a post here and I made some modifications but I don't understand whythe table can't be found.

Re: Error: Table not Found

avinashelite — Tue, 28 Jul 2015 12:42:13 GMT

can you share the sample data too??

I think you need to check whether the data is in the date format or not otherwise the date function won't work on top of that....

check with the Mindate, Maxdate ?? whether the its on date format or not

Re: Error: Table not Found

jonathandienst — Tue, 28 Jul 2015 12:43:00 GMT

Which line is triggering the error?

Does the email table exist before this script is run?

Re: Error: Table not Found

puttemans — Tue, 28 Jul 2015 12:50:13 GMT

Hi,

Is 'aytogenerate' a valid syntax? Possible that the table therefore is not created?

Regards,

Re: Error: Table not Found

Tue, 28 Jul 2015 13:07:18 GMT

In the beggining. I have loaded the excel file with two fields: 1.mailinglist 2.mytable (contains timestamp). The work sheet in excel file is called mails. I don't find how to upload the excel file.

The data format is: M/D/YYYY h:mm:ss in Greek (Also I correct the code at this part but it's not working again)

Thank you for your quick response

Re: Error: Table not Found

sasiparupudi1 — Tue, 28 Jul 2015 13:11:48 GMT

May be you can try this

set the variable vMinDate= to minimum date and vMaxDate to your maximum date from your excel file (exclude the time part)

Dates:

LOAD

Floor(Min(TimeStamp#(mytable, 'MM/DD/YYYY hh:mm:ss'))) AS MinDate,

Floor(Max(TimeStamp#(mytable, 'MM/DD/YYYY hh:mm:ss'))) AS MaxDate

RESIDENT emails;

LET vMinDate =num(date(date#('01/20/2005','MM/DD/YYYY'),'MM/DD/YYYY'));

LET vMaxDate = num(date(date#('07/28/2015','MM/DD/YYYY'), 'MM/DD/YYYY'));

TempCalendar:

LOAD $(vMinDate) + Iterno()-1 As Num,

Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

TempDate AS mytable,

Hour(TempDate) AS CalenderHour,

Minute(TempDate) AS CalenderMinute,

Date( Floor(TempDate)) AS CalendarDate,

Day(TempDate) AS CalendarDayOfMonth,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear,

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

//DROP Table Dates;

HTH

Sasi

Re: Error: Table not Found

Tue, 28 Jul 2015 13:12:44 GMT

the excel file I use

Re: Error: Table not Found

Tue, 28 Jul 2015 13:22:15 GMT

Thank you for your response!
I tried that but it still throws me error.

maybe the problem is in the excel file. I submit it if you want to take a look

Re: Error: Table not Found

jonathandienst — Tue, 28 Jul 2015 13:26:49 GMT

I repeat = does the email table exists before this script is run?

Re: Error: Table not Found

avinashelite — Tue, 28 Jul 2015 13:38:34 GMT

please find the attachment for the solution :

Re: Error: Table not Found

Tue, 28 Jul 2015 14:54:10 GMT

thank you for your response! The problem was that I hadn't load the table before I ran the script!
But I now I have problem with the hours! It uploads the dates but not hours and minutes. If you have any suggestions I would be grateful

Re: Error: Table not Found

johanlindell — Tue, 28 Jul 2015 14:59:20 GMT

I suggest you STORE your temporary tables to files and check what data you have in your TempDate field in in the TempCalendar table. probably there's something wrong with this field..

Re: Error: Table not Found

avinashelite — Wed, 29 Jul 2015 04:34:38 GMT

did you saw my attachment ??

Re: Error: Table not Found

Wed, 29 Jul 2015 10:22:35 GMT

Yes I saw it later! Thank you very very very much! I am working on it!