Qlik Community

App Development

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

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
jpjust
Creator III
Creator III

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
Creator
Creator

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

jpjust
Creator III
Creator III
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

View solution in original post

stevejoyce
Creator
Creator

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
Creator III
Creator III
Author

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

jpjust
Creator III
Creator III
Author

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