topic Re: Master Calendar "Field not found" Error in QlikView

Master Calendar "Field not found" Error

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

Hi Experts,

I am trying to create Canonical Date Almost all i have completed successfully but when I am trying to create the master calendar for Canonical Date column I am getting this error

Can you guys please check the qvw and help me out from this, Why i am getting this error and what is my mistake

Thank you in advance

Re: Master Calendar "Field not found" Error

andrey_krylov — Mon, 17 Sep 2018 07:43:55 GMT

Try this one

LOAD Date('2016-01-01' + IterNo() - 1) as [Date] AutoGenerate 1 While Date('2016-01-01' + IterNo() - 1) <= Date('2020-01-01');

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 07:46:32 GMT

Sorry I attached wrong qvw. This is the updated one.

Please finf it and help me out from this

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 07:48:49 GMT

Hi Andrey,

Now i attached one qvw please check that and based on that please give the suggestion

Thank you in advance

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 08:16:07 GMT

Replace this on all Tabs:

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

with this

Load
  '$(vMinDate)' + IterNo() - 1 as DateNum
AutoGenerate 1
While '$(vMinDate)' + IterNo() - 1 <= '$(vMaxDate)';

hope this helps

Re: Master Calendar "Field not found" Error

andrey_krylov — Mon, 17 Sep 2018 08:18:22 GMT

The field CanonicalDate is empty, therefore mindate and maxdate for mastercaledar are null. Check creating [DateBridge] table and applymap() statement.

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 08:37:11 GMT

Hi Frank,

I have replaced with this in my qvw but when i am trying to create master calendar year i am getting empty like this

I am attaching qvw also please check it and let me know what was the mistake

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 08:52:59 GMT

Check Andreys answer.

Indeed your CanonicalDate Field is empty!

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 08:55:20 GMT

Yeah, I have verified that also. But i am not getting any clue why my Canonical Date Field is empty.

Can you guys please help me out from this

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 08:55:58 GMT

Yeah, I have verified But i am not getting any clue why my Canonical Date Field is empty.

Can you guys please help me out from this

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 09:08:30 GMT

Add this in script in your DateBridge Table. Your Mapping Tables are missing

EnterCalendar:
Mapping Load CandidateID, EnterDate Resident MRMqvd; 


JobEndCalendar:
Mapping Load CandidateID, JobEndDate Resident MRMqvd; 


placementCalendar:
Mapping Load CandidateID, placementdate Resident MRMqvd;


plcdate:
Mapping Load CandidateID, plcdate Resident MRMqvd;


offerCalendar:
Mapping Load CandidateID, offerdate Resident MRMqvd;


DeclineCalendar:
Mapping Load CandidateID, DeclineDate Resident MRMqvd;




DateBridge:


     Load CandidateID, Applymap('EnterCalendar',CandidateID,Null()) as CanonicalDate, 'EnterDate' as DateType
          Resident MRMqvd;


     Load CandidateID, Applymap('JobEndCalendar',CandidateID,Null()) as CanonicalDate, 'JobEndDate' as DateType
          Resident MRMqvd;


     Load CandidateID, Applymap('placementCalendar',CandidateID,Null()) as CanonicalDate, 'placementdate' as DateType
          Resident MRMqvd;


     Load CandidateID, Applymap('plcCalendar',CandidateID,Null()) as CanonicalDate, 'plcdate' as DateType
          Resident MRMqvd;
          
     Load CandidateID, Applymap('offerCalendar',CandidateID,Null()) as CanonicalDate, 'offerdate' as DateType
          Resident MRMqvd;


     Load CandidateID, Applymap('DeclineCalendar',CandidateID,Null()) as CanonicalDate, 'DeclineDate' as DateType
          Resident MRMqvd;

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 09:24:01 GMT

Hi Frank,

I already tried with this and created one text object with this expression

=Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},DateType={'plcdate'}>}CandidateID)

And i have created one list box also Year (Master Calendar)

Now If i select 2018 in listbox it is showing '0' But the actual answer is 72

Please check for this

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 10:03:51 GMT

try this:

Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh','Manoj Sathiyamurthy'},DateType={'plcdate'},plcYear={'2018'}>}CandidateID)

If you take plcYear instead of Year for listbox selection and add Manoj Sathiyamurthy to Recruiter Field than i get 72!

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 10:04:24 GMT

try this:

Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh','Manoj Sathiyamurthy'},DateType={'plcdate'},plcYear={'2018'}>}CandidateID)

If you take plcYear instead of Year for listbox selection and add Manoj Sathiyamurthy to Recruiter Field than i get 72!

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 10:07:41 GMT

Hi Frank,

We Did Small mistake, i.e In Mapping tables we gave wrong table name for plccalendar. But then also it is giving wrong output for the master calendar year. But if i select plcYear 2018 it is giving correct.

Please check for that once

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 10:15:58 GMT

No with out manoj the output value is 72. I have noticed one thing January month is going to grey. if january month also linked to plcYear the output will come correct. and I can't write plcYear={'2018'} in my expression because Year column is the list box

Please see below screenshots:

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 11:09:37 GMT

If i refer to your QVD File, then without Manoj the value is 71!

And with Manoj the value is 72!

Also the connection between Year and plcYear seems to be incorrect.

If i select plcYear = 2018 then in Year Field 2014,2015,2017,2018 are connected!

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 11:22:52 GMT

Yeah Frank, It is showing like this... I am also not getting why it is happening like this.

Previously, I made similar report. In that report i prepared some charts also it is correct and user approved also. But user wants one table which is not possible with that dash board (here i attached please check once)

change is:

User wants one straight table with these fields

Jobid, Department, TitleName, No.of Positions for those 4 recruiters( I have created table till this). Now he wants three more columns those are EnterDate, PlacementDate, count of days between EnterDate and PlacementDate.

If i got the solution for this also the problem will be solved

Can please check for this and help me out with better one

Re: Master Calendar "Field not found" Error

Frank_Hartmann — Mon, 17 Sep 2018 12:07:51 GMT

Sorry, I have no clue.

Better close this thread and open a new one with attaching your File.

Re: Master Calendar "Field not found" Error

bvssudhakar — Mon, 17 Sep 2018 12:53:17 GMT

Hi Frank,

Thank you for your support. and I will open new thread