topic Re: Error While loading Master Calendar in QlikView

Error While loading Master Calendar

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I am connected to my database and trying to load master calendar. But while loading i am getting error as "Member Table Not found". Below is the script which i am loading.Is there any mistake in the script

DateTemp:

LOAD min(MakeDate(Year(registrationdate),Month(registrationdate),Day(registrationdate))) as Temp_Date

Resident Member;

LOAD max(MakeDate(Year(registrationdate),Month(registrationdate),Day(registrationdate))) as Temp_Date

Resident Member;

LOAD min(MakeDate(Year(transactiondatetime),Month(transactionDateTime),Day(transactiondatetime)))as Temp_Date

Resident transaction;

LOAD max(MakeDate(Year(transactiondatetime),Month(transactiondatetime),Day(transactiondatetime)))as Temp_Date

Resident transaction;

/*Creating Master calendar */

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Temp_Date) as minDate,

max(Temp_Date) as maxDate

Resident DateTemp;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

// DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

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

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendarDaily:

Load

Recno()as DataWeekNo,

TempDate as Date,

Date(TempDate-29) as RollStart,

TempDate as RollEnd,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

WeekDay(TempDate)As weekday

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

DROP Table Temp;

Drop table DateTemp;

Re: Error While loading Master Calendar

prma7799 — Fri, 26 May 2017 04:05:40 GMT

Please check whether Member table name correct or not ....

Re: Error While loading Master Calendar

prma7799 — Fri, 26 May 2017 04:09:54 GMT

Please check this ....

Re: Error While loading Master Calendar

Anonymous — Fri, 26 May 2017 04:13:37 GMT

I Cheked it. The Table name is correct.

Re: Error While loading Master Calendar

prma7799 — Fri, 26 May 2017 04:15:00 GMT

Please share sample app...

It will better to help you.

Re: Error While loading Master Calendar

Anonymous — Fri, 26 May 2017 04:22:04 GMT

here is the complete script. Hope this helps

Load

Clientnumberphonenumber,clientstatus,registrationdate as Date,registrationstorelocation,gender,dateofbirth;

sql select Clientnumberphonenumber,clientstatus,registrationdate,registrationstorelocation,gender,dateofbirth from [dbo].[Member];

load translip,clientnumberphonenumber,storelocation,Date;

sql select translip,clientnumberphonenumber,storelocation,transactiondatetime as Date from dbo.transaction where transactiondatetime>='01-Apr-2014 00:00:00' and transactiondatetime<='30-Apr-2014 23:59:59';

DateTemp:

LOAD min(MakeDate(Year(registrationdate),Month(registrationdate),Day(registrationdate))) as Temp_Date

Resident [Member];

LOAD max(MakeDate(Year(registrationdate),Month(registrationdate),Day(registrationdate))) as Temp_Date

Resident [Member];

LOAD min(MakeDate(Year(transactiondatetime),Month(transactionDateTime),Day(transactiondatetime)))as Temp_Date

Resident [transaction];

LOAD max(MakeDate(Year(transactiondatetime),Month(transactiondatetime),Day(transactiondatetime)))as Temp_Date

Resident [transaction];

/*Creating Master calendar */

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Temp_Date) as minDate,

max(Temp_Date) as maxDate

Resident DateTemp;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

// DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

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

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendarDaily:

Load

Recno()as DataWeekNo,

TempDate as Date,

Date(TempDate-29) as RollStart,

TempDate as RollEnd,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

WeekDay(TempDate)As weekday

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

DROP Table Temp;

Drop table DateTemp;

Re: Error While loading Master Calendar

prma7799 — Fri, 26 May 2017 04:25:17 GMT

Try this,

[Member]:

Load

Clientnumberphonenumber,clientstatus,registrationdate as Date,registrationstorelocation,gender,dateofbirth;

sql select Clientnumberphonenumber,clientstatus,registrationdate,registrationstorelocation,gender,dateofbirth from [dbo].[Member];

[transaction]:

load translip,clientnumberphonenumber,storelocation,Date;

Re: Error While loading Master Calendar

Anonymous — Fri, 26 May 2017 04:36:12 GMT

Great !! It worked out.

Thanks a lot

Re: Error While loading Master Calendar

prma7799 — Fri, 26 May 2017 04:39:57 GMT

Please check here you have to replace for registrationdate to Date

LOAD min(MakeDate(Year(Date),Month(Date),Day(Date))) as Temp_Date

Thanks

Re: Error While loading Master Calendar

prma7799 — Fri, 26 May 2017 04:41:56 GMT

if you get right answer then please mark right answer and close the thread .

It will helpful for other members.