topic Re: Finding Range in Script in App Development

Finding Range in Script

rajeshqvd — Wed, 22 Dec 2021 20:29:53 GMT

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

Re: Finding Range in Script

tresB — Fri, 21 Dec 2018 07:42:09 GMT

Try like:

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

Re: Finding Range in Script

rajeshqvd — Fri, 21 Dec 2018 09:47:17 GMT

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

Re: Finding Range in Script

tresB — Fri, 21 Dec 2018 10:04:47 GMT

Could you post your input and expected output ?

Re: Finding Range in Script

rajeshqvd — Fri, 21 Dec 2018 10:17:17 GMT

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

Re: Finding Range in Script

sunny_talwar — Fri, 21 Dec 2018 12:30:31 GMT

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

Re: Finding Range in Script

rajeshqvd — Fri, 21 Dec 2018 13:07:32 GMT

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

Re: Finding Range in Script

rajeshqvd — Mon, 15 Apr 2019 06:05:17 GMT

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

Re: Finding Range in Script

sunny_talwar — Mon, 15 Apr 2019 11:28:13 GMT

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

Re: Finding Range in Script

rajeshqvd — Mon, 15 Apr 2019 12:03:00 GMT

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

Done sunny Please check..Thanks.