topic Count Unique Field in Script in QlikView

Count Unique Field in Script

Fri, 22 Jul 2011 11:05:54 GMT

I want to count the number of distinct times my unique key is found in my table in a particular row and for it to indicate this by means of a '1' if more then 1 field is found and '0' if only 1 is found. I've tried this but get the following error

TempData1:
LOAD *,
TempName&WkEndDate&TimesheetNo&CategoryDesc&Fabs(ChargeValue)&Fabs(Hours) as Key
Resident Invoicing;
Drop Table Invoicing;

TempData2:
LOAD *,
count(Distinct Key) as Check
Resident TempData1
Group by TempName,WkEndDate,TimesheetNo,CategoryDesc;
Drop Table TempData1;

The error i get is this "Invalid Expression: TempData2:......" So I assume it is crashing because of the count. I'm not sure what fields I need to have in my group by to fix this so kinda lost. I've also tried the following

TempData1:
LOAD *,
TempName&WkEndDate&TimesheetNo&CategoryDesc&Fabs(ChargeValue)&Fabs(Hours) as Key
Resident Invoicing;
Drop Table Invoicing;

TempData2:
LOAD *,
If(FieldValueCount('Key')>1,1,0) as Check
Resident TempData1
Group by TempName,WkEndDate,TimesheetNo,CategoryDesc;
Drop Table TempData1;

This doesnt give me what I require either. Please help

Cheers,Byron

Count Unique Field in Script

swuehl — Fri, 22 Jul 2011 11:30:10 GMT

Hi,

maybe something like:

TempData2:
LOAD *,
If(peek(Key)=Key, peek(Check)+1, 0) as Check
Resident TempData1
order by Key ;
Drop Table TempData1;

Idea is to sort table by Key, if Key in previous row equals Key in current row, increase Check by 1, else set to 0.

so I would expect e.g.

Key, Check

A , 0

B, 0

B, 1

B, 2

C, 0

I have not understand what you want to do with the group by, I understood you wanted to check for global uniqueness within your table. If not, please clarify.

Stefan

Count Unique Field in Script

swuehl — Fri, 22 Jul 2011 11:54:43 GMT

me again,

I guess you want to do only kind of sanity check that your Keys are really unique.

For that, you might also just compare count (Key) with count (distinct Key) or even easier, use a listbox of field Key with frequency.

Stefan

Count Unique Field in Script

Fri, 22 Jul 2011 12:10:20 GMT

Thanks Swuehl

That has given me some ideas. Ultimately what I want is for 'B = 0 through to B = 2' to all come up as B = 1 each. The reason for this is that I actually want to remove these fields from my table when I do my expression. If they all equal 1 it should be pretty easy to remove Any suggestions?

THANKS

Count Unique Field in Script

swuehl — Fri, 22 Jul 2011 13:02:04 GMT

Hm yes, but do you want to remove the duplicate Keys completely from the table during load or just check in a

chart expression if the Key is not unique?

For latter,

you could use a simple condtion in expression, I think

like (when using dimension Key)

= if(sum(Check)=0, EXPRESSION)

Stefan

Count Unique Field in Script

Sat, 23 Jul 2011 18:31:08 GMT

Thanks for your help with this stefan. My data is pretty messy so have some sorting and ordering by to get right and then the above will work perfectly

Cheers,
Byron