topic Re: Running count of the Filed in the Script in QlikView

Running count of the Filed in the Script

Mon, 22 Oct 2012 13:38:18 GMT

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

Re: Running count of the Filed in the Script

flipside — Mon, 22 Oct 2012 13:57:04 GMT

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

Re: Running count of the Filed in the Script

jeffmartins — Mon, 22 Oct 2012 14:11:19 GMT

Hi Siri P,

You can use peek and previous fuction to do this.

See the attached file, hope it helps you.

Regards

Re: Running count of the Filed in the Script

Mon, 22 Oct 2012 14:27:36 GMT

Thanks

Re: Running count of the Filed in the Script

Mon, 22 Oct 2012 14:27:51 GMT

Thanks Jeff

Re: Running count of the Filed in the Script

Mon, 13 May 2013 10:09:08 GMT

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

Re: Running count of the Filed in the Script

flipside — Mon, 13 May 2013 10:26:50 GMT

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

Re: Running count of the Filed in the Script

Mon, 13 May 2013 13:42:28 GMT

flipside

Thanks it worked perfectly after i sorted my data

Re: Running count of the Filed in the Script

rwunderlich — Mon, 13 May 2013 14:00:05 GMT

An easier solution may be

autonumber(recno(), ID) as Running_count

That does not require your data to be presorted.

-Rob