Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator

How can i take distinct values in summary expression

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

6 Replies
maxgro
MVP
MVP

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

];

geogou1973
Creator
Creator
Author

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 ?

maxgro
MVP
MVP

yes auto.... is slow

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

// test data

a:

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;

b:

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;

geogou1973
Creator
Creator
Author

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

maxgro
MVP
MVP

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

a:

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,101,21,1

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

];

b:

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

1.jpg

Anonymous
Not applicable

Hi,

Try

See Here

distinct value.

Regards

Neetha