topic Re: How to Loop and Increment Value in QlikView

How to Loop and Increment Value

alvinford — Tue, 19 Apr 2016 09:55:11 GMT

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.

Step	StepCode	Date	StepCount
OutSold	B224395	12/3/2013 10:46:22	1
PRC	B224395	12/3/2013 10:46:21	2
	B224395	12/3/2013 10:28:37
	B224395	12/3/2013 10:28:36
	B224395	12/3/2013 10:28:19
	B224395	12/3/2013 10:28:18
	B224395	12/3/2013 10:26:28
	B224395	12/3/2013 10:26:27
	B224395	12/3/2013 10:11:27
	B224395	12/3/2013 10:10:54
	B224395	12/3/2013 10:10:52
	B224395	12/3/2013 10:10:27
	B224395	12/3/2013 10:09:55
	B224395	12/3/2013 10:09:54
	B224395	11/3/2013 15:36:01
	B224395	11/3/2013 15:36:00
	B224395	11/3/2013 15:34:27
	B224395	11/3/2013 15:34:26
	B224395	11/3/2013 15:17:13
	B224395	11/3/2013 15:17:12
	B224395	11/3/2013 15:17:00
Transfer	B224395	11/3/2013 15:16:59	2
	B224395	11/3/2013 14:35:01
	B224395	11/3/2013 14:35:00
	B224395	11/3/2013 14:27:15
	B224395	11/3/2013 14:27:14
	B224395	4/3/2013 14:03:17
	B224395	4/3/2013 14:03:16
	B224395	27/2/2013 18:09:52
	B224395	27/2/2013 18:09:51
	B224395	27/2/2013 14:05:02

Regards,

Alvin

Re: How to Loop and Increment Value

sunny_talwar — Tue, 19 Apr 2016 10:01:04 GMT

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

Re: How to Loop and Increment Value

vikasmahajan — Tue, 19 Apr 2016 10:03:58 GMT

Re: How to Loop and Increment Value

swuehl — Tue, 19 Apr 2016 10:35:35 GMT

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;

Re: How to Loop and Increment Value

Peter_Cammaert — Tue, 19 Apr 2016 12:10:23 GMT

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

Re: How to Loop and Increment Value

alvinford — Tue, 19 Apr 2016 14:47:19 GMT

Hi Swuehi,

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

Cheers !!

Regards,

Alvin.

Re: How to Loop and Increment Value

swuehl — Tue, 19 Apr 2016 16:24:18 GMT

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

Qlik Community Tip: Marking Replies as Correct or Helpful