Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Siri P,
You can use peek and previous fuction to do this.
See the attached file, hope it helps you.
Regards
Thanks
Thanks Jeff
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
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];
flipside
Thanks it worked perfectly after i sorted my data
An easier solution may be
autonumber(recno(), ID) as Running_count
That does not require your data to be presorted.
-Rob