topic Re: Problem With Multiple Date Columns in QlikView

Problem With Multiple Date Columns

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

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

Re: Problem With Multiple Date Columns

Frank_Hartmann — Mon, 10 Sep 2018 14:11:28 GMT

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

Re: Problem With Multiple Date Columns

bvssudhakar — Mon, 10 Sep 2018 14:14:51 GMT

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

Re: Problem With Multiple Date Columns

Frank_Hartmann — Mon, 10 Sep 2018 14:17:29 GMT

Did you select any Filters?

Re: Problem With Multiple Date Columns

bvssudhakar — Mon, 10 Sep 2018 14:20:45 GMT

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

Re: Problem With Multiple Date Columns

bvssudhakar — Mon, 10 Sep 2018 14:23:50 GMT

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

Re: Problem With Multiple Date Columns

Frank_Hartmann — Mon, 10 Sep 2018 14:43:10 GMT

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

Re: Problem With Multiple Date Columns

bvssudhakar — Mon, 10 Sep 2018 14:57:30 GMT

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

Re: Problem With Multiple Date Columns

Frank_Hartmann — Mon, 10 Sep 2018 15:02:09 GMT

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!

Re: Problem With Multiple Date Columns

bvssudhakar — Mon, 10 Sep 2018 15:04:49 GMT

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

Re: Problem With Multiple Date Columns

Frank_Hartmann — Mon, 10 Sep 2018 15:08:15 GMT

Sorry my previous post was not correct. e.g. the Dates for offerdate was not recognized as a Datefield and therefor the connection to the MC was not established! My previous post was concerning the first script i have posted but that was my mistake 🙂

Re: Problem With Multiple Date Columns

bvssudhakar — Tue, 11 Sep 2018 06:40:57 GMT

Thank you

Re: Problem With Multiple Date Columns

bvssudhakar — Tue, 11 Sep 2018 08:47:49 GMT

Hi Frank,

Can you please tell me how to convert that master calendar as hierarchy calendar. What changes are required