Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
Highlighted
rajeshqvd
Contributor

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

Re: Finding Range in Script

Try like:

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

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

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

Re: Finding Range in Script

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