topic How can i take distinct values in summary expression in QlikView

How can i take distinct values in summary expression

geogou1973 — Sat, 21 Feb 2015 08:55:43 GMT

Hello

I have a database that has

date , store , hour , sales

01/02/2015 , 101 , 19 , 10000

some times brings a second record with another sales amount

date , store , hour , sales

01/02/2015 , 101 , 19 , 10000

01/02/2015 , 101 , 19 , 15000

how can i take only the first record in a chart with the expression sum(sales)

I attached a qvw file.

Thank you in advance

Re: How can i take distinct values in summary expression

maxgro — Sat, 21 Feb 2015 09:15:37 GMT

one solution could be;

- add a field in the load to identify first record by date,store,hour

- the chart expression is sum({$ <id={1}>} sales)

load *, AutoNumber(rowno(),date&'-'&store&'-'&hour) as id

inline [

date,store,hour,sales

01/02/2015,101,21,10000

01/02/2015,102,21,15000

01/02/2015,103,21,20000

01/02/2015,101,21,30000

];

Re: How can i take distinct values in summary expression

geogou1973 — Sat, 21 Feb 2015 09:38:41 GMT

Hello Massimo.

The problem is that the database has more than 3 million records and the process takes too long to complete the reload with autonumber . Is there any other way with expression formula to take only these values ?

Re: How can i take distinct values in summary expression

maxgro — Sat, 21 Feb 2015 10:32:26 GMT

yes auto.... is slow

I tried in another way, this is the script, 5 millions of record, 75 sec

// test data

load

rowno() as id,

date(makedate(2015)+floor(rand()*30)) as date,

101 + floor(rand()*10) as store,

floor(rand()*24) as hour,

floor(rand()*100000) as sales

autogenerate 5000000;

NoConcatenate load

if(store<>Peek(store) or hour<>Peek(hour) or date<>Peek(date),1,0) as flagfirst

//AutoNumber(rowno(),date&'-'&store&'-'&hour) as flagfirst

Resident a

order by date,store,hour,id;

DROP Table a;

Re: How can i take distinct values in summary expression

geogou1973 — Sat, 21 Feb 2015 12:35:29 GMT

I don't understand where this script helps to avoid the duplicate records by date - store - hour.

Re: How can i take distinct values in summary expression

maxgro — Sat, 21 Feb 2015 14:06:05 GMT

the script add a field, flagfirst, to your table to identify the distinct record by date - store - hour (1 = first record in table by date -store-hour, 0 duplicate)

the chart expression use set analysis sum( {$ <flagfirst={1}>} sales) to filter the distinct record by ...

SCRIPT

load *, rowno() as id

inline [

date,store,hour,sales

01/02/2015,101,21,10000

01/02/2015,102,21,15000

01/02/2015,103,21,20000

01/02/2015,101,21,1

01/02/2015,102,21,1

01/02/2015,103,21,1

01/02/2015,104,21,100

01/02/2015,104,22,100

01/02/2015,104,22,1

02/02/2015,104,22,100

02/02/2015,104,22,1

];

NoConcatenate load

if(store<>Peek(store) or hour<>Peek(hour) or date<>Peek(date),1,0) as flagfirst

Resident a

order by date,store,hour,id;

DROP Table a;

RESULT

Re: How can i take distinct values in summary expression

Anonymous — Sat, 21 Feb 2015 18:44:46 GMT

Hi,

Try

See Here

distinct value.

Regards

Neetha