Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bvssudhakar
Contributor II

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
Honored Contributor II

Re: Problem With Multiple Date Columns

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
Honored Contributor II

Re: Problem With Multiple Date Columns

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

Re: Problem With Multiple Date Columns

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
Honored Contributor II

Re: Problem With Multiple Date Columns

Did you select any Filters?

bvssudhakar
Contributor II

Re: Problem With Multiple Date Columns

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

bvssudhakar
Contributor II

Re: Problem With Multiple Date Columns

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
Honored Contributor II

Re: Problem With Multiple Date Columns

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

bvssudhakar
Contributor II

Re: Problem With Multiple Date Columns

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
Honored Contributor II

Re: Problem With Multiple Date Columns

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

Re: Problem With Multiple Date Columns

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