Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
what is the expected output? I am confused
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!
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;