Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alvinford
Contributor III
Contributor III

How to Loop and Increment Value

Hi ,

I have the following data, please refer the attached excel for complete data. It has the columns Step, StepCode and Date and I need to derive the StepCount column. Based on the Date column I have to increment the count of Step column.

Request your help to achieve this . Please refer the attached Excel. Thank You in Advance.

    

StepStepCodeDateStepCount
OutSoldB22439512/3/2013 10:46:221
PRCB22439512/3/2013 10:46:212
B22439512/3/2013 10:28:37
B22439512/3/2013 10:28:36
B22439512/3/2013 10:28:19
B22439512/3/2013 10:28:18
B22439512/3/2013 10:26:28
B22439512/3/2013 10:26:27
B22439512/3/2013 10:11:27
B22439512/3/2013 10:10:54
B22439512/3/2013 10:10:52
B22439512/3/2013 10:10:27
B22439512/3/2013 10:09:55
B22439512/3/2013 10:09:54
B22439511/3/2013 15:36:01
B22439511/3/2013 15:36:00
B22439511/3/2013 15:34:27
B22439511/3/2013 15:34:26
B22439511/3/2013 15:17:13
B22439511/3/2013 15:17:12
B22439511/3/2013 15:17:00
TransferB22439511/3/2013 15:16:592
B22439511/3/2013 14:35:01
B22439511/3/2013 14:35:00
B22439511/3/2013 14:27:15
B22439511/3/2013 14:27:14
B2243954/3/2013 14:03:17
B2243954/3/2013 14:03:16
B22439527/2/2013 18:09:52
B22439527/2/2013 18:09:51
B22439527/2/2013 14:05:02

Regards,

Alvin

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

LOAD Step,

    StepCode,

    Date

FROM

(ooxml, embedded labels, table is Sheet2, filters(

Replace(1, top, StrCnd(null))

));

LOAD *,

  If(Previous(Step)=Step,Peek(StepCount), AutoNumber(Recno(),StepCode&Step)) as StepCount

Resident INPUT

ORDER BY StepCode, Date asc;

DROP TABLE INPUT;

View solution in original post

6 Replies
sunny_talwar

Not sure I understand your output? How are you coming up with the StepCount field? What is the logic?

vikasmahajan

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
swuehl
MVP
MVP

Try

LOAD Step,

    StepCode,

    Date

FROM

(ooxml, embedded labels, table is Sheet2, filters(

Replace(1, top, StrCnd(null))

));

LOAD *,

  If(Previous(Step)=Step,Peek(StepCount), AutoNumber(Recno(),StepCode&Step)) as StepCount

Resident INPUT

ORDER BY StepCode, Date asc;

DROP TABLE INPUT;

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Nice trick, the AutoNumber with RecNo, StepCode and Step.

alvinford
Contributor III
Contributor III
Author

Hi Swuehi,

Thank You for your help .. It works perfectly ...

Cheers !!

Regards,

Alvin.

swuehl
MVP
MVP

Please close this thread by marking an answer as correct if your request is resolved.

Qlik Community Tip: Marking Replies as Correct or Helpful