topic Re: Problem with Aggregation in QlikView

Problem with Aggregation

Wed, 11 May 2016 14:28:56 GMT

Hi everybody, I have a dude with aggregation. I need to do something but I don't know how.

I need count the quantity of Document_ID that appear more that once and use_data=2 and dispatch>'01/05/2016'

So, in this example, I must count 3 (4833237, 4833242, 4833258) because are the document_id that be repeated.

And not must count: 4833218, 4833248 and 4833255 because appear once.

Thanks in advance

Re: Problem with Aggregation

sunny_talwar — Wed, 11 May 2016 14:34:09 GMT

May be like this:

Count(DISTINCT {<Document_ID = {"=Count({<use_data = {2}, dispatch = {'>$(=Date(MonthStart(Today())))'}>}Document_ID) > 1"}, use_data = {2}, dispatch = {'>$(=Date(MonthStart(Today())))'}>} Document_ID)

Re: Problem with Aggregation

Wed, 11 May 2016 14:46:20 GMT

No, no works!

Re: Problem with Aggregation

idogridish2 — Wed, 11 May 2016 14:48:31 GMT

Hi aleira27

you can use Group By in the script,

by creating a new table:

New_Temp_Table_Name:

LOAD

Document_ID,

count(Document_ID) as [Doc Count]

resident Old_Table_Name

Where use_data=2 and dispatch>'01/05/2016'

Group By Document_ID;

noconcatanate

New_Table_Name:

LOAD

Document_ID,

[Doc Count]

resident New_Temp_Table_Name

where [Doc Count]>=2;

Drop Table New_Temp_Table_Name;

Hope that helps

Cheers

Re: Problem with Aggregation

sunny_talwar — Wed, 11 May 2016 14:48:47 GMT

Would you be able to share a sample to look at it?

Re: Problem with Aggregation

Wed, 11 May 2016 15:09:04 GMT

I attach a file with the sample.

I need count the quantity of Document_ID that appear more that once and use_data=2 and dispatch_time_floor>'01/05/2016' (variable called vFechaFiltro)

Thanks

Re: Problem with Aggregation

idogridish2 — Wed, 11 May 2016 15:25:53 GMT

for text object you can this:

=sum(aggr(count(DISTINCT {$<USE_DATA={'2'},DISPATCH_TIME={'>01/05/2016'}>}DOCUMENT_ID),DOCUMENT_ID))

Re: Problem with Aggregation

sunny_talwar — Wed, 11 May 2016 15:26:13 GMT

Can you may be make the file a little smaller

Preparing examples for Upload - Reduction and Data Scrambling

Re: Problem with Aggregation

Wed, 11 May 2016 17:32:44 GMT

Are there not possibilities without create new tables?

Because I have a problem.. the date '01/05/2016' are in a variable in the application and the idea is that if the user change the date the result changes, because I need calculate in an expresion.

I attach the .qvw file to see mi example

Re: Problem with Aggregation

Wed, 11 May 2016 17:33:09 GMT

Can't you download?