Problem with Aggregation

[#1]

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

[sunny_talwar]

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)

[#3]

No, no works!

[idogridish2 · Accepted solution]

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

[sunny_talwar]

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

[#6]

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

[idogridish2]

for text object you can this:

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

[sunny_talwar]

Can you may be make the file a little smaller

Preparing examples for Upload - Reduction and Data Scrambling

[#9]

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

[#10]

Can't you download?