Qlik Community

Qlik Sense App Development

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

Highlighted
rajeshqvd
Contributor II

Finding Range in Script

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)  

IDDataOutput
111aCycle1
111bCycle1
111cCycle1
111dCycle1
111ABCycle1
111jCycle2
111kCycle2
111ABCycle2
111klCycle3
111hgCycle3
111hggCycle3
111ABCycle3
222aCycle1
222cCycle1
222dCycle1
222eCycle1
222ABCycle1
222aaCycle2
222ffCycle2
222ABCycle2
Labels (1)
1 Solution

Accepted Solutions

Re: Finding Range in Script

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
];
9 Replies
MVP
MVP

Re: Finding Range in Script

Try like:

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

Re: Finding Range in Script

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

MVP
MVP

Re: Finding Range in Script

Could you post your input and expected output ?
rajeshqvd
Contributor II

Re: Finding Range in Script

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

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
Contributor II

Re: Finding Range in Script

WOW.... Sunny your fast and Best...Thank you for your support
rajeshqvd
Contributor II

Re: Finding Range in Script

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

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

rajeshqvd
Contributor II

Re: Finding Range in Script