Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
bvssudhakar
Creator III
Creator III

Problem With Multiple Date Columns

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


1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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

View solution in original post

12 Replies
Frank_Hartmann
Master II
Master II

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
Creator III
Creator III
Author

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
Master II
Master II

Did you select any Filters?

bvssudhakar
Creator III
Creator III
Author

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

bvssudhakar
Creator III
Creator III
Author

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
Master II
Master II

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
Creator III
Creator III
Author

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
Master II
Master II

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
Creator III
Creator III
Author

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