Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jumiprado
New Contributor III

Correct sum dates!

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.

%IDfromtoto-from+1
309
3891205/07/201605/08/201632
3891206/08/201606/09/201632
3891207/09/201607/10/201631
3891208/10/201608/11/201632
3891209/11/201609/12/201631
3891210/12/201623/12/201614
3891224/12/201607/05/2017135
3891208/03/201708/03/20171
3891209/03/201709/03/20171
307

Thanks!

marcowedel

stalwar1

franky_h79

pcammaert

kush141087

vadim.grab

3 Replies

Re: Correct sum dates!

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?

Re: Correct sum dates!

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

Re: Correct sum dates!

Hi,

one possible solution might be:

QlikCommunity_Thread_258721_Pic1.JPG

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;

see also:

Creating Reference Dates for Intervals

hope this helps

regards

Marco

Community Browser