Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Running count of the Filed in the Script

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

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Running count of the Filed in the Script

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

8 Replies
flipside
Valued Contributor II

Re: Running count of the Filed in the Script

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
Contributor II

Re: Running count of the Filed in the Script

Hi Siri P,

You can use peek and previous fuction to do this.

See the attached file, hope it helps you.

Regards

Not applicable

Re: Running count of the Filed in the Script

Thanks

Not applicable

Re: Running count of the Filed in the Script

Thanks Jeff

Not applicable

Re: Running count of the Filed in the Script

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
Valued Contributor II

Re: Running count of the Filed in the Script

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];

Not applicable

Re: Running count of the Filed in the Script

flipside

Thanks it worked perfectly after i sorted my data

Re: Running count of the Filed in the Script

An easier solution may be

autonumber(recno(), ID) as Running_count

That does not require your data to be presorted.

-Rob

Community Browser