topic Re: How to create a ID_COUNT field? in QlikView

How to create a ID_COUNT field?

btrompetter — Thu, 18 Aug 2011 13:41:13 GMT

Hello,

I have a table with sales per adress

Now I need a count field wich is 1 for every distinct adress.

I must replace the count (distinct ad_id) with sum (ad_id_count)

How is the peek statement to create this field?

thanks for help

Best wishes

Bastian

Re: How to create a ID_COUNT field?

sbaldwin — Thu, 18 Aug 2011 14:01:38 GMT

Hi, if you sorted the data as it was loaded by the address field you could use the following to check if the new record was the same as the one before, if it is different then 1 otherwise 0?

if(address = peek(address),0,1) as ad_id_count

Thanks

Steve

Re: How to create a ID_COUNT field?

btrompetter — Fri, 19 Aug 2011 07:35:22 GMT

Hey

thanks for your reply.

But I have to deal with 150 million records.Is there another way to create such field?

What about a sepreate table with only the AD_ID and a count_filed, did this have the same effect? Or Is a count(DISTINCT) faster?

Best wishes

Bastian