Date comparison issue in qlik script

[chaitanyajami]

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

[edwin]

sample data would be most helpful

[edwin]

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

[chaitanyajami]

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;

[edwin]

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? 

[chaitanyajami]

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;

[edwin · Accepted solution]

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;

[chaitanyajami]

Thank you very much Edwin, it worked perfect for me

[edwin]

yw