Creating a Cycle Field in Script

Badzreyes00 · ‎2020-11-20

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_No	trxn_date	trxn_id	-14 Date	-7 Date	End Date	Cycle	Expected Value
1001	9/18/2020	1001-9/18/2020	9/17/2020	9/24/2020	10/01/2020	1	1
1001	9/19/2020	1001-9/19/2020	9/17/2020	9/24/2020	10/01/2020	2	1
1001	9/20/2020	1001-9/20/2020	9/17/2020	9/24/2020	10/01/2020	3	1
1001	9/21/2020	1001-9/21/2020	9/17/2020	9/24/2020	10/01/2020	4	1
1001	9/22/2020	1001-9/22/2020	9/17/2020	9/24/2020	10/01/2020	5	1
1001	9/23/2020	1001-9/23/2020	9/17/2020	9/24/2020	10/01/2020	6	1
1001	9/24/2020	1001-9/24/2020	9/17/2020	9/24/2020	10/01/2020	7	1
1001	9/25/2020	1001-9/25/2020	9/17/2020	9/24/2020	10/01/2020	8	1
1001	9/26/2020	1001-9/26/2020	9/17/2020	9/24/2020	10/01/2020	9	1
1001	9/27/2020	1001-9/27/2020	9/17/2020	9/24/2020	10/01/2020	10	1
1001	9/28/2020	1001-9/28/2020	9/17/2020	9/24/2020	10/01/2020	11	1
1001	9/29/2020	1001-9/29/2020	9/17/2020	9/24/2020	10/01/2020	12	1
1001	9/30/2020	1001-9/30/2020	9/17/2020	9/24/2020	10/01/2020	13	1
1001	10/1/2020	1001-10/1/2020	9/17/2020	9/24/2020	10/01/2020	14	1
1001	10/6/2020	1001-10/6/2020	10/5/2020	10/12/2020	10/19/2020	43	2
1001	10/7/2020	1001-10/7/2020	10/5/2020	10/12/2020	10/19/2020	44	2
1001	10/8/2020	1001-10/8/2020	10/5/2020	10/12/2020	10/19/2020	45	2
1001	10/9/2020	1001-10/9/2020	10/5/2020	10/12/2020	10/19/2020	46	2
1001	10/10/2020	1001-10/10/2020	10/5/2020	10/12/2020	10/19/2020	47	2
1001	10/11/2020	1001-10/11/2020	10/5/2020	10/12/2020	10/19/2020	48	2
1001	10/12/2020	1001-10/12/2020	10/5/2020	10/12/2020	10/19/2020	49	2
1001	10/13/2020	1001-10/13/2020	10/5/2020	10/12/2020	10/19/2020	50	2
1001	10/14/2020	1001-10/14/2020	10/5/2020	10/12/2020	10/19/2020	51	2
1001	10/15/2020	1001-10/15/2020	10/5/2020	10/12/2020	10/19/2020	52	2
1001	10/16/2020	1001-10/16/2020	10/5/2020	10/12/2020	10/19/2020	53	2
1001	10/17/2020	1001-10/17/2020	10/5/2020	10/12/2020	10/19/2020	54	2
1001	10/18/2020	1001-10/18/2020	10/5/2020	10/12/2020	10/19/2020	55	2
1001	10/19/2020	1001-10/19/2020	10/5/2020	10/12/2020	10/19/2020	56	2

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

Regards

MayilVahanan · ‎2020-11-20

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
Please close the thread by marking correct answer & give likes if you like the post.

Badzreyes00 · ‎2020-11-23

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 · ‎2020-11-23

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
Please close the thread by marking correct answer & give likes if you like the post.

Badzreyes00 · ‎2020-11-23

Hi @MayilVahanan

Thank you for quick response, I really appreciate it.

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

MayilVahanan · ‎2020-11-23

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:

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

MayilVahanan · ‎2020-11-23

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
];

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

cycle dimension

PEEK as expression

Script