Finding Range in Script

[rajeshqvd]

Hello, need help on create new field in script below output required where ever data = 'AB' that recognize Cycle1 after that need to increment Cycle'N'(Cycle1,Cycle2,Cycle3)  

ID	Data	Output
111	a	Cycle1
111	b	Cycle1
111	c	Cycle1
111	d	Cycle1
111	AB	Cycle1
111	j	Cycle2
111	k	Cycle2
111	AB	Cycle2
111	kl	Cycle3
111	hg	Cycle3
111	hgg	Cycle3
111	AB	Cycle3
222	a	Cycle1
222	c	Cycle1
222	d	Cycle1
222	e	Cycle1
222	AB	Cycle1
222	aa	Cycle2
222	ff	Cycle2
222	AB	Cycle2

[tresesco]

Try like:

Load *,
	If(Data='AB', 'Cycle'&AutoNumber(Data&RowNo()),Output) as OutputNew

[rajeshqvd]

thanks for your response...NO its giving continues increment and that output field is required out put 

[tresesco]

Could you post your input and expected output ?

[rajeshqvd]

This is the Data
ID Data
111 a
111 b
111 c
111 d
111 AB
111 j
111 k
111 AB
111 kl
111 hg
111 hgg
111 AB
222 a
222 c
222 d
222 e
222 AB
222 aa
222 ff
222 AB
this is required Output
Output
Cycle1
Cycle1
Cycle1
Cycle1
Cycle1
Cycle2
Cycle2
Cycle2
Cycle3
Cycle3
Cycle3
Cycle3
Cycle1
Cycle1
Cycle1
Cycle1
Cycle1
Cycle2
Cycle2
Cycle2

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD RowNo() as RowNum,
	 *,
	 If(ID = Previous(ID), If(Previous(Data) = 'AB', RangeSum(Peek('CycleNum'), 1), Peek('CycleNum')), 1) as CycleNum,
	 'Cycle' & If(ID = Previous(ID), If(Previous(Data) = 'AB', RangeSum(Peek('CycleNum'), 1), Peek('CycleNum')), 1) as Output;
LOAD * INLINE [
    ID, Data
    111, a
    111, b
    111, c
    111, d
    111, AB
    111, j
    111, k
    111, AB
    111, kl
    111, hg
    111, hgg
    111, AB
    222, a
    222, c
    222, d
    222, e
    222, AB
    222, aa
    222, ff
    222, AB
];

[rajeshqvd]

WOW.... Sunny your fast and Best...Thank you for your support

[rajeshqvd]

HI Sunny,
I am facing below issue.

ID Data Output Output should be
222 AB Cycle1 Cycle1
222 AB Cycle2 Cycle2
222 AB Cycle3 Cycle3
222 e Cycle4 Cycle3
222 ffg Cycle4 Cycle3
222 aa Cycle4 Cycle3
222 ff Cycle4 Cycle3
222 df Cycle4 Cycle3

but in other scenario it coming correctly
ID Data Output
111 a Cycle1
111 b Cycle1
111 c Cycle1
111 d Cycle1
111 AB Cycle1
111 j Cycle2
111 k Cycle2
111 AB Cycle2
111 kl Cycle3
111 hg Cycle3
111 hgg Cycle3
111 AB Cycle3

[sunny_talwar]

Please open a new thread to explain what the issue is and we can go from there?

[rajeshqvd]

https://community.qlik.com/t5/New-to-Qlik-Sense/Finding-Cycles/m-p/1569372#M132755

Done sunny Please check..Thanks.