Running count of the Filed in the Script

[#1]

Hi Folks,

I am trying to attach the running count of the filed in the script. Please find the below sample example:

ID Sales

A  100,250

B  390

C  30,345,360

I want load the above table into below:

ID Sales Running_Cnt

A  100      1

A  250      2

B  390      1

C  30        1

C  345      2

C  360      3

I created the Sales by using subfield(Sales,','), but I am unable to attach the Running_Cnt to the table.

Please find the attched application.

Thanks & Regards,

Srikanth

[flipside · Accepted solution]

Is this what you need ...

Data:

LOAD * INLINE [

ID, Sales

A,  100|250

B,  390

C,  30|345|360];

RunningTotals:

LOAD

    ID,

    SubField(Sales,'|',iterNo()) as SalesEntry,

    iterNo() as SalesEntryRow

resident Data

while iterNo() <= SubStringCount(Sales,'|')+1;

(My example uses pipe-delimited entries due to Inline data load, but will work with comma-delimited field).

flipside

[jeffmartins]

Hi Siri P,

You can use peek and previous fuction to do this.

See the attached file, hope it helps you.

Regards

[#4]

Thanks

[#5]

Thanks Jeff

[#6]

Hi Flipside

I was reviewing the Running Count script you wrote for Srikanth, hence i have the same question.  How would you script the running script if your dataset was like below and you had no pipes on your inline table?

ID Sales

A  100

A  250

B  390

C  30

C  345

C  360

Thanks your for your consideration.

Regards

Selby

[flipside]

Hi Selby,

Probably this approach is better for your scenario ...

RawData:

Load

    ID,

    Sales,

    If(ID = Peek(ID),

        1 + Peek(RunningCount_ID),

        1)                                 as RunningCount_ID;    

load * inline [

ID, Sales

A,  100

A,  250

B,  390

C,  30

C,  345

C,  360];

flipside

EDIT: Forgot to mention this assumes your data is already sorted as you require. For example, try the above with this inline data and check the count function is as you wish ...

load * inline [

ID, Sales

A,  100

A,  250

B,  390

C,  30

C,  345

A,  900

C,  360];

[#8]

flipside

Thanks it worked perfectly after i sorted my data

[rwunderlich]

An easier solution may be

autonumber(recno(), ID) as Running_count

That does not require your data to be presorted.

-Rob