Skip to main content

App Development

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
jpjust
Specialist
Specialist

Numbering Requirement

Hi,

I have a requirement like this. How do I derive the new column?

Customer   Alert         NewColumn

A                     1                      1

A                     0                      0

A                     0                      0

A                     1                       1

A                     1                      2

A                     1                     3

Thanks

1 Solution

Accepted Solutions
Saravanan_Desingh

Try something like this,

tab1:
LOAD *, If(Customer=Peek(Customer) And Peek(Alert)=0 And Alert=1,1,If(Alert=0,0,RangeSum(Peek(NewColumn),1))) As NewColumn;
LOAD RecNo() As RowID,* INLINE [
    Customer, Alert
    A, 1
    A, 0
    A, 0
    A, 1
    A, 1
    A, 1
];

commQV03.PNG

View solution in original post

6 Replies
stevejoyce
Specialist II
Specialist II

This is not a requirement.  You posted a sample set of data.  Do you want to explain your requirement?

jpjust
Specialist
Specialist
Author

Hi Steve,

Sorry for not being very clear.

Wherever the Alert for an given customer ends at 0 and starts with 1, the new column should be sequenced accordingly as 1,2,3 as I showed in the new column.

Thanks

Saravanan_Desingh

Try something like this,

tab1:
LOAD *, If(Customer=Peek(Customer) And Peek(Alert)=0 And Alert=1,1,If(Alert=0,0,RangeSum(Peek(NewColumn),1))) As NewColumn;
LOAD RecNo() As RowID,* INLINE [
    Customer, Alert
    A, 1
    A, 0
    A, 0
    A, 1
    A, 1
    A, 1
];

commQV03.PNG

stevejoyce
Specialist II
Specialist II

Ok, you can use peek for this.

 

source_data:
load Customer, Alert inline [
Customer, Alert, NewColumn

A, 1, 1,

A, 0, 0,

A, 0, 0,

A, 1, 1,

A, 1, 2,

A, 1, 3,

B, 1, 1,

]
;


data:
Load
Customer
,Alert
,if(peek(Customer) = Customer,
If(Alert=1, peek(NewColumn) + 1, 0)
,if(Alert=1, 1, 0)) as NewColumn


resident source_data
order by Customer asc;


drop table source_data;
exit script;

 

 

jpjust
Specialist
Specialist
Author

Thank you so much Saravanan. Your logic really helped a lot.

jpjust
Specialist
Specialist
Author

Thank you so much Steve for your help and the approach you provided. Really appreciate it.