Problem With Multiple Date Columns

[bvssudhakar]

Hi Experts,

Here I am attaching qvw file. Please find it.

I have multiple date columns. I have combined and linked master calendar. Now I want to create Text Objects

I have create 1 Text object with this expression:

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

This is giving Correct Output

I have 4 Text Objects remaining to create like this

2nd i tried with this expression

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

But it is giving the out put 0 when i am selecting 2016 or 2017 or 2018...........

Why it is giving like this i am not understanding i have linked with offerdate column also

Can you guys please check the script and help me out from this

Thank you in advance

[Frank_Hartmann]

try this in script and have a look if the Datefields are linked correctly now:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';


//EXECUTE "C:\Program Files\QlikView\QV.exe" /r "D:\Qlikview\RMS\MRM.qvw";


MRMqvd:
LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings, 
     EnterDate,
     //Date(Date#(EnterDate, 'DD-MMM-YYYY')) as Date,
  JobEndDate,
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City, 
     placementdate, 
     plcdate, 
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName, 
     offerdate, 
     DeclineDate
FROM
MRM.QVD
(qvd);




Date:


LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings, 
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City, 
     Date#(Date(left(EnterDate,10), 'MM/DD/YYYY'),'DD/MM/YYYY') as LinkDate, 
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName,
     'EnterDate' as Date_Flag

Resident MRMqvd;


LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings,     
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City, 
     Date#(Date(left(JobEndDate,10), 'MM/DD/YYYY'),'DD/MM/YYYY') as LinkDate, 
     //Date(JobEndDate, 'DD/MM/YYYY') as LinkDate,
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName, 
     'JobEndDate' as Date_Flag

Resident MRMqvd;


LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings, 
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City,
     Date#(Date(left(placementdate,10), 'MM/DD/YYYY'),'DD/MM/YYYY') as LinkDate,  
     //Date(placementdate, 'DD/MM/YYYY') as LinkDate,
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName, 
     'placementdate' as Date_Flag

Resident MRMqvd;


LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings, 
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City, 
     Date#(Date(left(plcdate,10), 'MM/DD/YYYY'),'DD/MM/YYYY') as LinkDate, 
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName, 
     'plcdate' as Date_Flag


Resident MRMqvd;



LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings, 
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City, 
     Date#(Date(left(offerdate,10), 'MM/DD/YYYY'),'DD/MM/YYYY') as LinkDate, 
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName, 
     'offerdate' as Date_Flag


Resident MRMqvd;



LOAD jobid, 
     TitleName, 
     ishotjob, 
     NoOpenings, 
     JobClassid, 
     Location, 
     Department, 
     CandidateID, 
     City,
     Date#(Date(left(DeclineDate,10), 'MM/DD/YYYY'),'DD/MM/YYYY') as LinkDate, 
     PeopleSourceName, 
     Recruiter, 
     CandidateName, 
     skillName, 
     'DeclineDate' as Date_Flag


Resident MRMqvd;


DROP Table MRMqvd;

hope this helps

[bvssudhakar]

Hi Frank,

Thank you for the response, I made the changes in my script. all fields are linked but now the problem is

when i am trying to create the text object with this expression it is giving wrong output

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

For that expression the out put value is 71

Can you please find where i did the mistake

[Frank_Hartmann]

Did you select any Filters?

[bvssudhakar]

Yes, When i select the 2018 filter the output should be 71 some thing

[bvssudhakar]

previously it gave correct value but that time linking happens only with plc date column & JobEnd column. Now linking applied for all fields but the output value is wrong

[Frank_Hartmann · Accepted solution]

try this and also notice the change in dateformat in MasterCalendar:

SET ThousandSep=',';  
SET DecimalSep='.';  
SET MoneyThousandSep=',';  
SET MoneyDecimalSep='.';  
SET MoneyFormat='$#,##0.00;($#,##0.00)';  
SET TimeFormat='h:mm:ss TT';  
SET DateFormat='M/D/YYYY';  
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';  
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';  
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';  


//EXECUTE "C:\Program Files\QlikView\QV.exe" /r "D:\Qlikview\RMS\MRM.qvw";  


MRMqvd:  
LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,   
     EnterDate,  
     //Date(Date#(EnterDate, 'DD-MMM-YYYY')) as Date,  
  JobEndDate,  
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,   
     placementdate,   
     plcdate,   
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,   
     offerdate,   
     DeclineDate  
FROM  
MRM.QVD  
(qvd);  




Date:  


LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,   
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,   
     Date#(Date(left(EnterDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,   
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,  
     'EnterDate' as Date_Flag  

Resident MRMqvd;  


LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,       
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,   
     Date#(Date(left(JobEndDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,   
     //Date(JobEndDate, 'MM/DD/YYYY') as LinkDate,  
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,   
     'JobEndDate' as Date_Flag  

Resident MRMqvd;  


LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,   
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,  
     Date#(Date(left(placementdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,    
     //Date(placementdate, 'MM/DD/YYYY') as LinkDate,  
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,   
     'placementdate' as Date_Flag  

Resident MRMqvd;  


LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,   
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,   
     Date#(Date(left(plcdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,   
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,   
     'plcdate' as Date_Flag  


Resident MRMqvd;  



LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,   
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,   
     Date#(Date(left(offerdate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,   
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,   
     'offerdate' as Date_Flag  


Resident MRMqvd;  



LOAD jobid,   
     TitleName,   
     ishotjob,   
     NoOpenings,   
     JobClassid,   
     Location,   
     Department,   
     CandidateID,   
     City,  
     Date#(Date(left(DeclineDate,10), 'MM/DD/YYYY'),'MM/DD/YYYY') as LinkDate,   
     PeopleSourceName,   
     Recruiter,   
     CandidateName,   
     skillName,   
     'DeclineDate' as Date_Flag  


Resident MRMqvd;  


DROP Table MRMqvd;  


Temp:
LOAD
    Max(LinkDate) as MaxDate,
    Min(LinkDate) as MinDate


Resident Date;


let vMaxDate = Floor(YearEnd(Peek('MaxDate',-1,'Temp')));
let vMinDate = Floor(YearStart(Peek('MinDate',-1,'Temp')));


drop table Temp;






Calendar:
Load
  Date(DateNum, 'MM/DD/YYYY') as LinkDate,
  Day(DateNum) as Day,
  Month(DateNum) as Month,
  Week(DateNum) as Week,
  WeekDay(DateNum)as WeekDay,
  'Q' & Ceil(Month(DateNum)/3) as Quarter,
  Year(DateNum) as Year;


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

[bvssudhakar]

Hi Frank,

Awesome, It's working perfectly.  Thank you so much.

Can you please explain me what is the mistake of mine, So that next time on wards i will cross check

[Frank_Hartmann]

your datefields were mixed up. some datefields matched the dateformat of your Mastercalender others didnt.

You could have seen it by looking  to the LinkDate field when selecting different Date_Flags. Some Formats were like  MM/DD/YYYY others like DD/MM/YYYY!

hope this was helpful!

[bvssudhakar]

Thank you frank, Next time on wards i will verify those things also