topic Counting the number of times a word appears at the detail level in QlikView

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

Wed, 01 Sep 2010 15:43:56 GMT

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

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

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 15:47:31 GMT

Hello,

Create a new field like the following:

If(Previous(producttype) = 'washer', RangeSum(Peek(WasherCount), 1)) AS CountNo

Hope that helps.

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

Wed, 01 Sep 2010 16:03:02 GMT

Miguel, It kind of works. I need the count to show up on the same row as the record itself. Is this how it works for you?

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

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 16:22:47 GMT

I'm afraid I'm missing something. The code I wrote is to create a new field when you are LOADing a table (from a excel file, a qvd file or a sql statement), so it will be showed when you create a chart or table with that new field. When do you want to show that count?

Regards

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

Wed, 01 Sep 2010 16:32:06 GMT

Washercount is the new field I want to create during the load.
When does this field get created?
If(Previous(producttype) = 'washer', RangeSum(Peek(WasherCount), 1)) AS CountNo

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

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 16:44:40 GMT

Sorry, it should look like

Table: LOAD Invoice, detailid, producttype, If(Previous(producttype) = 'washer', RangeSum(Peek(WasherCount), 1)) AS WasherCount;SQL SELECT Invoice, detailid, producttype FROM YourDatabaseTable;

(in case you are loading from a database)

Hope that helps

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

Wed, 01 Sep 2010 17:27:01 GMT

Miguel, I tried it. The counts are not landing on the rows with the word "Washer" and it does not seem to recognize the unique Invoice # and InvoiceDetail# combination.

I'm getting Invoice #1, Detail 1, producttype = "bottle", washercount = 1
Invoice #1, Detail 2 producttype = "washer, washercount = 0

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

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 17:31:58 GMT

Hi,

You probably need an additional if to the code:

Table:LOAD Invoice, detailid, producttype, If(Invoice = Previous(Invoice), If(Previous(producttype) = 'washer', RangeSum(Peek(WasherCount), 1))) AS WasherCount;SQL SELECT Invoice, detailid, producttypeFROM YourDatabaseTable;

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

Wed, 01 Sep 2010 17:42:27 GMT

I tried this also. The counts are not landing on the "washer" rows and the counts are not accurate at the invoice level. I was thinking something like Aggr(count(if(producttype = 'washer', 1, 0)Invoice) as WasherCount , but this does not work.

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

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 21:57:37 GMT

Hello,

Now I'm pretty sure I'm missing something. Please attach the sql statement you are using to load your data into QlikView for further advice, as I don't understand how are you loading your data.

Regards.

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

Wed, 01 Sep 2010 22:30:38 GMT

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

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

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 07:26:42 GMT

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?

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

Thu, 02 Sep 2010 15:01:23 GMT

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

Thu, 02 Sep 2010 15:06:16 GMT

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?

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

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 16:22:03 GMT

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.

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

Thu, 02 Sep 2010 16:55:40 GMT

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.

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

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 17:09:04 GMT

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

Regards.

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

Thu, 02 Sep 2010 18:38:47 GMT

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.

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

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 20:30:21 GMT

Code edited again.

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

Thu, 02 Sep 2010 20:57:59 GMT

Miguel,

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