Correct sum dates! - Qlik Community

jumiprado · ‎2017-04-28

Hey guys!

I need your help again! I have the following table and i need to sum the days but if you can see, the sum for the days returns me 309 but i need 307 because the two last rows have dates from and to included into the previous row (in bolds)

How can i have the value in red.

%ID	from	to	to-from+1
			309
38912	05/07/2016	05/08/2016	32
38912	06/08/2016	06/09/2016	32
38912	07/09/2016	07/10/2016	31
38912	08/10/2016	08/11/2016	32
38912	09/11/2016	09/12/2016	31
38912	10/12/2016	23/12/2016	14
38912	24/12/2016	07/05/2017	135
38912	08/03/2017	08/03/2017	1
38912	09/03/2017	09/03/2017	1
			307

Thanks!

marcowedel‌

stalwar1‌

franky_h79‌

pcammaert‌

kush141087‌

vadim.grab‌

Kushal_Chawda · ‎2017-04-29

I did not completely understand your below statement

"two last rows have dates from and to included into the previous row"

Can you please elaborate more?

marcus_sommer · ‎2017-04-29

I think you need to flag or correct this within the script maybe with something like this:

t0:

load %ID, from, to from source;

t1:

load

%ID, from as from_old, to as to_old,

if(from < previous(to), previous(to) + 1, from) as from,

if(to < previous(to), from - 1, to) as to

resident t0 order by %ID, from;

t2:

load *, to-from+1 as [to-from] resident t1;

drop tables t0, t1;

I'm not sure that this will be catch all possible cases and you might need to extend the logic to further checks within the if-loops or some more loadings which will flag and/or remove certain records if they aren't valid in your requirement.

- Marcus

MarcoWedel · ‎2017-04-30

Hi,

one possible solution might be:

table1:
LOAD RecNo() as %Key,
    %ID,
    from,
    to
FROM [https://community.qlik.com/thread/258721] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 11))));

tabDateLink:
LOAD %Key,
    Date(from+IterNo()-1) as Date
Resident table1
While from+IterNo()-1 <= to;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabDateLink;