Counting the number of times a word appears at the detail level

[#1]

Hi All,

I would like to count the number of times a certain word appears at the detail level in the load script.

Data example: Invoice #1, detailid #30, producttype = 'washer'
Invoice #1, detailid #35, producttype = 'washer'

Answer example: Invoice #1, detailid #30, producttype = 'washer', washer count = #1
Invoice #1, detailid #35, producttype = 'washer', washer count = #2
Invoice #2, detailid #7, producttype = 'washer', washer count = #1
Invoice #2, detailid #22, producttype = 'washer', washer count = #2
Invoice #2, detailid #40, producttype = 'washer', washer count = #3

Any help would be appreciated

[#2]

Change_Log:

LOAD

"ENTER_DATE" AS Log_EnterDate,

If(Previous(TYPE) = 'REV', RangeSum(Peek(RevCount), 1)) AS RevCount,

"PROJ_NO" AS ProjNbr, //Log contains multiple entries for each ProjNbr

"TYPE" AS Log_Type,

"Z_ID" AS Log_ID //Unique

;

SQL SELECT

"ENTER_DATE",

"PROJ_NO",

"TYPE",

"Z_ID"

FROM "PROJ_EST";

[Miguel_Angel_Baeyens]

Hello,

So you have now a RevCount that is returning the count of all records which TYPE = 'REV'. Is that correct? What else do you need to count?

[#4]

[#5]

Miguel, Each row with a type REV should have a sequential count that begins at 1 for each new ProjNbr.
All other types should have a zero. Is this possible to do with the previous and peek functions?

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

I'm afraid I forgot the "order by" part:

Count:
LOAD *, 
     IF(Log_Type = 'REV', IF(ProjNbr = PREVIOUS(ProjNbr), RANGESUM(PEEK('COUNTER'), 1), 1), 0) AS COUNTER
RESIDENT Table
ORDER BY Log_Type, ProjNbr, Z_ID DESC;

Hope we got it.

[#7]

Miguel, The counts are landing on the REV lines now, but the order is wrong.
It has to be Order by Proj_Nbr then by ID.

[Miguel_Angel_Baeyens]

Thanks for pointing. I'll edit the script in my previous post to keep the order.

Regards.

[#9]

Miguel, It works, but the order is incorrect. The order has to be by Proj_Nbr, then Z_ID.
Some count #1s should be count #2s and some #2s should be #1s.

[Miguel_Angel_Baeyens]

Code edited again.

[#11]

Miguel,

Thanks....it works.....you have done it.
You deserve a medal for not quitting.