Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Badzreyes00
Contributor III
Contributor III

Creating a Cycle Field in Script

Hi All,

Hope you are all safe and well. I already tried searching in the community but failed to found one, I am trying to create a Cycle field in my table using peek, basically it is group by Field_No and End Date

I am using 

Table2:
Load
Field_No,
"-7 Date",
"-14 Date",
"End Date",
trxn_date,
if(Field_No=previous(Field_No),peek(Cycle)+1,1) as Cycle
resident Table1
Order BY
Field_No ASC, "End Date" ASC
;

Field_Notrxn_datetrxn_id-14 Date-7 DateEnd DateCycleExpected Value
10019/18/20201001-9/18/20209/17/20209/24/202010/01/202011
10019/19/20201001-9/19/20209/17/20209/24/202010/01/202021
10019/20/20201001-9/20/20209/17/20209/24/202010/01/202031
10019/21/20201001-9/21/20209/17/20209/24/202010/01/202041
10019/22/20201001-9/22/20209/17/20209/24/202010/01/202051
10019/23/20201001-9/23/20209/17/20209/24/202010/01/202061
10019/24/20201001-9/24/20209/17/20209/24/202010/01/202071
10019/25/20201001-9/25/20209/17/20209/24/202010/01/202081
10019/26/20201001-9/26/20209/17/20209/24/202010/01/202091
10019/27/20201001-9/27/20209/17/20209/24/202010/01/2020101
10019/28/20201001-9/28/20209/17/20209/24/202010/01/2020111
10019/29/20201001-9/29/20209/17/20209/24/202010/01/2020121
10019/30/20201001-9/30/20209/17/20209/24/202010/01/2020131
100110/1/20201001-10/1/20209/17/20209/24/202010/01/2020141
100110/6/20201001-10/6/202010/5/202010/12/202010/19/2020432
100110/7/20201001-10/7/202010/5/202010/12/202010/19/2020442
100110/8/20201001-10/8/202010/5/202010/12/202010/19/2020452
100110/9/20201001-10/9/202010/5/202010/12/202010/19/2020462
100110/10/20201001-10/10/202010/5/202010/12/202010/19/2020472
100110/11/20201001-10/11/202010/5/202010/12/202010/19/2020482
100110/12/20201001-10/12/202010/5/202010/12/202010/19/2020492
100110/13/20201001-10/13/202010/5/202010/12/202010/19/2020502
100110/14/20201001-10/14/202010/5/202010/12/202010/19/2020512
100110/15/20201001-10/15/202010/5/202010/12/202010/19/2020522
100110/16/20201001-10/16/202010/5/202010/12/202010/19/2020532
100110/17/20201001-10/17/202010/5/202010/12/202010/19/2020542
100110/18/20201001-10/18/202010/5/202010/12/202010/19/2020552
100110/19/20201001-10/19/202010/5/202010/12/202010/19/2020562

 

Thank you so much in advance. Have a good day!

 

Regards

6 Replies
MayilVahanan

Hi @Badzreyes00 

Try like below

LOAD *, if(Field_No&'|'&EndDate <> Previous(Field_No&'|'&EndDate), Peek('Cycle')+1, Alt(Peek('Cycle'),1)) as Cycle

Thanks & Regards,
Mayil Vahanan R
Badzreyes00
Contributor III
Contributor III
Author

Thank you @MayilVahanan !

It works but the cycle number continues even with different field no, It should restart the cycle count for each field no. I tweak the code a little bit but failed to come up with the desired value. Thank you so much in advance! Please see sample below

Field_no trxn_date trxn_id Start Date Cycle Expected Value
1001 9/18/2020 1001-9/18/2020 10/01/2020 1 1
1001 9/19/2020 1001-9/19/2020 10/01/2020 1 1
1001 9/20/2020 1001-9/20/2020 10/01/2020 1 1
1001 9/21/2020 1001-9/21/2020 10/01/2020 1 1
1001 9/22/2020 1001-9/22/2020 10/01/2020 1 1
1001 9/23/2020 1001-9/23/2020 10/01/2020 1 1
1001 9/24/2020 1001-9/24/2020 10/01/2020 1 1
1001 9/25/2020 1001-9/25/2020 10/01/2020 1 1
1001 9/26/2020 1001-9/26/2020 10/05/2020 2 2
1001 9/27/2020 1001-9/27/2020 10/05/2020 2 2
1001 9/28/2020 1001-9/28/2020 10/05/2020 2 2
1001 9/29/2020 1001-9/29/2020 10/05/2020 2 2
1001 9/30/2020 1001-9/30/2020 10/05/2020 2 2
1001 10/1/2020 1001-10/1/2020 10/05/2020 2 2
1002 9/18/2020 1002-9/18/2020 10/03/2020 3 1
1002 9/19/2020 1002-9/19/2020 10/03/2020 3 1
1002 9/20/2020 1002-9/20/2020 10/03/2020 3 1
1002 9/21/2020 1002-9/21/2020 10/03/2020 3 1
1002 9/22/2020 1002-9/22/2020 10/08/2020 4 2
1002 9/23/2020 1002-9/23/2020 10/08/2020 4 2
1002 9/24/2020 1002-9/24/2020 10/08/2020 4 2
1002 9/25/2020 1002-9/25/2020 10/08/2020 4 2
1002 9/26/2020 1002-9/26/2020 10/08/2020 4 2

MayilVahanan

Hi @Badzreyes00 

Try like below

LOAD *, If(Field_No <> Previous(Field_No),1, if(Field_No&'|'&startDate <> Previous(Field_No&'|'&startDate), Peek('Cycle')+1, Alt(Peek('Cycle'),1))) as Cycle;

Thanks & Regards,
Mayil Vahanan R
Badzreyes00
Contributor III
Contributor III
Author

Hi @MayilVahanan 

 

Thank you for quick response, I really appreciate it.

I tried applying the formula but the result is still the same 😞

MayilVahanan

Hi @Badzreyes00 

 

LOAD *, If(Field_No <> Previous(Field_No),1, if(Field_No&'|'&startDate <> Previous(Field_No&'|'&startDate), Peek('Cycle')+1, Alt(Peek('Cycle'),1))) as Cycle, Previous(Field_No&'|'&startDate) as PrevKey;
LOAD * INLINE [
Field_No, trxn_date, trxn_id, startDate
1001, 9/18/2020, 1001-9/18/2020, 10/1/2020
1001, 9/19/2020, 1001-9/19/2020, 10/1/2020
1001, 9/20/2020, 1001-9/20/2020, 10/1/2020
1001, 9/21/2020, 1001-9/21/2020, 10/1/2020
1001, 9/22/2020, 1001-9/22/2020, 10/1/2020
1001, 9/23/2020, 1001-9/23/2020, 10/1/2020
1001, 9/24/2020, 1001-9/24/2020, 10/1/2020
1001, 9/25/2020, 1001-9/25/2020, 10/1/2020
1001, 9/26/2020, 1001-9/26/2020, 10/5/2020
1001, 9/27/2020, 1001-9/27/2020, 10/5/2020
1001, 9/28/2020, 1001-9/28/2020, 10/5/2020
1001, 9/29/2020, 1001-9/29/2020, 10/5/2020
1001, 9/30/2020, 1001-9/30/2020, 10/5/2020
1001, 10/1/2020, 1001-10/1/2020, 10/5/2020
1002, 9/18/2020, 1002-9/18/2020, 10/3/2020
1002, 9/19/2020, 1002-9/19/2020, 10/3/2020
1002, 9/20/2020, 1002-9/20/2020, 10/3/2020
1002, 9/21/2020, 1002-9/21/2020, 10/3/2020
1002, 9/22/2020, 1002-9/22/2020, 10/8/2020
1002, 9/23/2020, 1002-9/23/2020, 10/8/2020
1002, 9/24/2020, 1002-9/24/2020, 10/8/2020
1002, 9/25/2020, 1002-9/25/2020, 10/8/2020
1003, 9/26/2020, 1002-9/26/2020, 10/8/2020
];

Result:

MayilVahanan_0-1606123704104.png

 

Thanks & Regards,
Mayil Vahanan R
MayilVahanan

HI @Badzreyes00 

Its working fine.

LOAD *, If(Field_No <> Previous(Field_No),1, if(Field_No&'|'&startDate <> Previous(Field_No&'|'&startDate), Peek('Cycle')+1, Alt(Peek('Cycle'),1))) as Cycle, Previous(Field_No&'|'&startDate) as PrevKey;
LOAD * INLINE [
Field_No, trxn_date, trxn_id, startDate
1001, 9/18/2020, 1001-9/18/2020, 10/1/2020
1001, 9/19/2020, 1001-9/19/2020, 10/1/2020
1001, 9/20/2020, 1001-9/20/2020, 10/1/2020
1001, 9/21/2020, 1001-9/21/2020, 10/1/2020
1001, 9/22/2020, 1001-9/22/2020, 10/1/2020
1001, 9/23/2020, 1001-9/23/2020, 10/1/2020
1001, 9/24/2020, 1001-9/24/2020, 10/1/2020
1001, 9/25/2020, 1001-9/25/2020, 10/1/2020
1001, 9/26/2020, 1001-9/26/2020, 10/5/2020
1001, 9/27/2020, 1001-9/27/2020, 10/5/2020
1001, 9/28/2020, 1001-9/28/2020, 10/5/2020
1001, 9/29/2020, 1001-9/29/2020, 10/5/2020
1001, 9/30/2020, 1001-9/30/2020, 10/5/2020
1001, 10/1/2020, 1001-10/1/2020, 10/5/2020
1002, 9/18/2020, 1002-9/18/2020, 10/3/2020
1002, 9/19/2020, 1002-9/19/2020, 10/3/2020
1002, 9/20/2020, 1002-9/20/2020, 10/3/2020
1002, 9/21/2020, 1002-9/21/2020, 10/3/2020
1002, 9/22/2020, 1002-9/22/2020, 10/8/2020
1002, 9/23/2020, 1002-9/23/2020, 10/8/2020
1002, 9/24/2020, 1002-9/24/2020, 10/8/2020
1002, 9/25/2020, 1002-9/25/2020, 10/8/2020
1002, 9/26/2020, 1002-9/26/2020, 10/8/2020
];

 

MayilVahanan_0-1606125147579.png

 

Thanks & Regards,
Mayil Vahanan R