Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jumiprado
New Contributor III

Accumulate days in a table

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

I have this table in excel

IDreasondate_fromdate_to
1A101/01/201730/01/2017
1A101/01/201714/01/2017
1B115/01/201715/02/2017
1C113/02/201728/02/2017
2A105/02/201707/02/2017
2B106/02/201715/02/2017


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


IDreasondate_fromdate_toQ_DAYSQ_DAYS_REASONTOTAL_Q_DAYS
1A101/01/201730/01/20173030

59

1A101/01/201714/01/201714059
1B115/01/201715/02/2017311659
1C113/02/201728/02/201716

13

59
2A105/02/201707/02/20173311
2B106/02/201715/02/201716811


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:

    

IDreasondate_fromdate_toQ_DAYSQ_DAYS_REASON
1A101/01/201730/01/2017            30.00                   30
1A101/01/201713/02/2017            14.00                   44
1F115/01/201715/02/2017              2.00                  31
1G313/02/201728/02/2017            13.00                  16

thanks!!!

stalwar1

3 Replies

Re: Accumulate days in a table

what is the expected output? I am confused

jumiprado
New Contributor III

Re: Accumulate days in a table

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!

MVP
MVP

Re: Accumulate days in a table

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;

Capture.PNG

Community Browser