Solved: Counting the number of times a word appears at the... - Qlik Community

Report Inappropriate Content · ‎2010-09-01

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

Miguel_Angel_Baeyens · ‎2010-09-02

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.

View solution in original post

Miguel_Angel_Baeyens · ‎2010-09-01

Hello,

Create a new field like the following:

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

Hope that helps.

Report Inappropriate Content · ‎2010-09-01

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?

Miguel_Angel_Baeyens · ‎2010-09-01

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

Report Inappropriate Content · ‎2010-09-01

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

Miguel_Angel_Baeyens · ‎2010-09-01

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

Report Inappropriate Content · ‎2010-09-01

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

Miguel_Angel_Baeyens · ‎2010-09-01

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;

Report Inappropriate Content · ‎2010-09-01

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.

Miguel_Angel_Baeyens · ‎2010-09-01

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