topic Re: Accumulate days in a table in QlikView

Accumulate days in a table

jumiprado — Sun, 02 Apr 2017 15:49:08 GMT

Hey guys! I need your help to translate a calculated field in excel to qlikview script.

I have this table in excel

ID	reason	date_from	date_to
1	A1	01/01/2017	30/01/2017
1	A1	01/01/2017	14/01/2017
1	B1	15/01/2017	15/02/2017
1	C1	13/02/2017	28/02/2017
2	A1	05/02/2017	07/02/2017
2	B1	06/02/2017	15/02/2017

I need to generate new fields that I could perform in Excel but in the script

ID	reason	date_from	date_to	Q_DAYS	Q_DAYS_REASON	TOTAL_Q_DAYS
1	A1	01/01/2017	30/01/2017	30	30	59
1	A1	01/01/2017	14/01/2017	14	0	59
1	B1	15/01/2017	15/02/2017	31	16	59
1	C1	13/02/2017	28/02/2017	16	13	59
2	A1	05/02/2017	07/02/2017	3	3	11
2	B1	06/02/2017	15/02/2017	16	8	11

For the field Q_DAYS I made the following formula in Excel: DATE_TO - DATE_FROM + 1

For the field TOTAL_Q_DAYS I made =+MAX(date_to)-MIN(DATE_from)+1

For the Field Q_DAYS_REASON i made the following: the Q_DAYS for the min value of (date_from) is the same and in the second field I made =+IF(QDAYS>previous(Q_DAYS),Date_to-previous(Date_to),0)

What i need to do in this field is calculate If this date_To is greater than the previous date_to, add up the difference between the dates_to

for example:

ID	reason	date_from	date_to	Q_DAYS	Q_DAYS_REASON
1	A1	01/01/2017	30/01/2017	30.00	30
1	A1	01/01/2017	13/02/2017	14.00	44
1	F1	15/01/2017	15/02/2017	2.00	31
1	G3	13/02/2017	28/02/2017	13.00	16

thanks!!!

stalwar1‌

Re: Accumulate days in a table

Kushal_Chawda — Sun, 02 Apr 2017 17:44:23 GMT

what is the expected output? I am confused

Re: Accumulate days in a table

jumiprado — Sun, 02 Apr 2017 18:50:33 GMT

kushal i need to generate a table like the second one, with the fields Q_DAYS, Q_DAYS_REASON and TOTAL_Q_DAYS

I made an excel like the attachment but i need to make the fields in a large table in qlikview script.

I hope I have explained well

thanks in advance!

Re: Accumulate days in a table

sunny_talwar — Mon, 03 Apr 2017 00:18:54 GMT

Try this

Table:

LOAD *,

Date(If(ID = Previous(ID), RangeMax(Peek('date_to'), Peek('max_date_to')), date_to)) as max_date_to,

If(ID = Previous(ID), If(date_to > Previous(date_to), RangeSum(date_to, -RangeMax(Peek('date_to'), Peek('max_date_to'))), 0), date_to - date_from + 1) as Q_DAYS_REASON;

LOAD RecNo() as SerialNo,

ID,

reason,

date_from,

date_to,

date_to - date_from + 1 as Q_DAYS

FROM

[https://community.qlik.com/thread/255647]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD ID,

Max(date_to) - Min(date_from) + 1 as TOTAL_Q_DAYS

Resident Table

Group By ID;