topic Re: Date comparison issue in qlik script in QlikView

Date comparison issue in qlik script

chaitanyajami — Thu, 04 Mar 2021 15:18:27 GMT

hi all,

I have dates as below , iam actually writing below script to load the Date ,GCIO_Weekend_Date which are less than or equal to Max_Date for each record but some dates are not returned , please help if anything need to be corrected here

GCIO_Dates:
LOAD [CR number],
Date,
Max_Date,
GCIO_Weekend_Date
Resident GCIO_All_Dates where GCIO_Weekend_Date <= Max_Date;

after execution getting missing dates for few records

Re: Date comparison issue in qlik script

edwin — Thu, 04 Mar 2021 15:59:54 GMT

sample data would be most helpful

Re: Date comparison issue in qlik script

edwin — Thu, 04 Mar 2021 16:02:49 GMT

i think i see it. those dates are not pulled in bec of this where:

GCIO_Weekend_Date <= Max_Date;

if you explain what you are trying to do or what the business rules are it might help

Re: Date comparison issue in qlik script

chaitanyajami — Thu, 04 Mar 2021 16:11:03 GMT

Hi ,actually for each cr number mentioned below there is a start and end date and I have generated the dates between the start and end date and for each date I'm returning the weekend date and also for each cr I'm getting max date ,so I want to load the weekend dates for each cr which are less than the max date so I'm comparing the weekend date with the max date for each cr but it's breaking.

GCIO_Dates:
LOAD [CR number],
Date,
Max_Date,
GCIO_Weekend_Date
Resident GCIO_All_Dates where GCIO_Weekend_Date <= Max_Date;

Re: Date comparison issue in qlik script

edwin — Thu, 04 Mar 2021 16:44:12 GMT

this is actually working the way it is coded. because it is not pulling weekend dates greater than max date

is maxdate a required field? or is it there just to ensure the weekend date is between the start and end date? is date required as well? can you give sample source data and expected result?

Re: Date comparison issue in qlik script

chaitanyajami — Thu, 04 Mar 2021 17:18:42 GMT

attached the sample data , below is the requirement

i need to find out the weekend dates impacted by the cr number so iam first extracted the dates from the timestamp by below

[CR number]

Date(Floor([Scheduled Start (UTC)]),'DD/MM/YYYY') as [Scheduled Start],
Date(Floor([Scheduled End (UTC)]),'DD/MM/YYYY') as [Scheduled End],

after that im generating the dates between start and end date and trying to find out the max date

and from there im trying to generate the weekend for the each date but suppose there is a cr started on today 04/03/2021 and ended on 10/03/2021 when i generate the dates and weekend dates below is the output

04/03/2021,07/03/2021

05/03/2021,07/03/2021

06/03/2021,07/03/2021

07/03/2021,07/03/2021

08/03/2021,14/03/2021

09/03/2021,14/03/2021

10/03/2021,14/03/2021

as per the data CR is closed on 10th feb so i dont want to report the weekend date 14/03/2021 for the i have used the condition Weekend date <= Max date which is 10/03/2021 in the above case

below is the code

GCIO_All_Dates_Temp:
LOAD [CR number],

if ([Scheduled Start (UTC)] = [Scheduled End (UTC)],[Scheduled End (UTC)],Date([Scheduled Start (UTC)]+iterno()-1)) as Date

resident GCIO while [Scheduled Start (UTC)]+IterNo()-1 <= [Scheduled End (UTC)];

left join (GCIO_All_Dates_Temp)

Max_Date:
Load [CR number],
Date(Max(Date),'DD/MM/YYYY')as Max_Date
Resident GCIO_All_Dates_Temp group by [CR number] ;

GCIO_All_Dates:
LOAD [CR number],
Date,
Max_Date,
Date(Floor(WeekEnd(Date,0,0))) as GCIO_Weekend_Date
Resident GCIO_All_Dates_Temp;

Drop table GCIO_All_Dates_Temp ;

NoConcatenate

GCIO_Dates:
LOAD [CR number],
Date,
Max_Date,
GCIO_Weekend_Date
Resident GCIO_All_Dates where GCIO_Weekend_Date <= Max_Date;

Drop Table GCIO_All_Dates;

Re: Date comparison issue in qlik script

edwin — Thu, 04 Mar 2021 18:45:00 GMT

Dates: load 'CR' & rowno() as CR, * inline [ start, end 04/03/2021,07/03/2021 05/03/2021,07/03/2021 06/03/2021,07/03/2021 07/03/2021,07/03/2021 08/03/2021,14/03/2021 09/03/2021,14/03/2021 10/03/2021,14/03/2021 ]; //thsi is to frame the start and end dates NoConcatenate minmax: load date(min(start)) as minDate, date(max(start)) as maxDate Resident Dates; Concatenate (minmax) load date(min(end)) as minDate, date(max(end)) as maxDate Resident Dates; //this joins the start end dates with teh individual dates cartesian; //then it filters for dates within teh range inner join (Dates) load Date(MinDate + iterno()-1) as Date, weekday(Date(MinDate + iterno()-1)) as Day, if(match(weekday(Date(MinDate + iterno()-1)),'Sat','Sun')>0, 'WeekEnd','WeekDay') as WeekEndOrNot while Date(MinDate + iterno()-1) <=MaxDate; load date(min(minDate)) as MinDate, date(max(maxDate)) as MaxDate resident minmax; //filters out non-weekend NoConcatenate NewData: load * resident Dates where Date >=start and Date<=end and WeekEndOrNot = 'WeekEnd'; drop table minmax, Dates;

Re: Date comparison issue in qlik script

edwin — Thu, 04 Mar 2021 18:48:17 GMT

Re: Date comparison issue in qlik script

chaitanyajami — Fri, 05 Mar 2021 09:12:10 GMT

Thank you very much Edwin, it worked perfect for me

Re: Date comparison issue in qlik script

edwin — Fri, 05 Mar 2021 14:06:04 GMT