topic Re: group by or cluster? in QlikView

group by or cluster?

cscherer — Thu, 16 Aug 2012 12:42:35 GMT

Hello,

I've got a problem with clustering data. I've got dates and orders and I want to know in the first steps, how many orders I've got per day - this works. In the second step, I want to know how many days I've got with 1-5 orders, 6-10 orders and so on.

It shall look like this:

I attached the .qvw

Thanks in advance,

Clemens

Re: group by or cluster?

cscherer — Thu, 16 Aug 2012 12:46:27 GMT

here is the picture:

Re: group by or cluster?

Thu, 16 Aug 2012 12:47:00 GMT

try

if(count(distinct order)<=5,'5',if(count(distinct order)<=10,'10','Other')) as dimension

Re: group by or cluster?

cscherer — Thu, 16 Aug 2012 12:50:30 GMT

thank you, but that is not what I mean. I need the "next step"

15 times <= 5

11 times <=10

...

Re: group by or cluster?

Thu, 16 Aug 2012 13:22:00 GMT

if(count(distinct order)<=5,'5',if(count(distinct order)<=10,'10',if(count(distinct order)<=20,'20','>20')))) as dimension

Re: group by or cluster?

Thu, 16 Aug 2012 13:23:50 GMT

or:

if(count(distinct order)<=5,'Days with 1 to 5 orders',if(count(distinct order)<=10,'Days with 6 to 10 orders ',if(count(distinct order)<=20,'Days with 11 to 20 orders','days with >20 orders')))) as dimension

Re: group by or cluster?

cscherer — Thu, 16 Aug 2012 13:45:39 GMT

it doesn't work. have you got the .qvw?

Re: group by or cluster?

Fri, 17 Aug 2012 06:45:50 GMT

ok try this:

Load Script:

Orders:

LOAD order,

date,

article

FROM

C:\test.xls

(biff, embedded labels, table is Sheet1$);

Summary:

Left Join(Orders)

Load date,

if(count(distinct order)<=5,

'days with 1 to 5 orders',

if(count(distinct order)<=10,

'days with 6 to 10 orders',

if(count(distinct order)<=20,

'days with 11 to 20 orders',

'days with >20 orders'))) as DayType

resident Orders group by date;

Table:

DayType as dimension

count(distinct date) as Expression

Re: group by or cluster?

cscherer — Fri, 17 Aug 2012 07:05:36 GMT

that`s it! thank you so much!

Re: group by or cluster?

cscherer — Fri, 17 Aug 2012 12:01:23 GMT

Hello,

one more question: is it possible to intricate more than 7 if-formulas? If not, I think I`ll need another solution for my problem...

Thanks,

regards,

Re: group by or cluster?

Fri, 17 Aug 2012 12:11:49 GMT

you can use 10000 but i would create a function that you call from load script

in macro (Tools-> Edit module)

function checkdays(qty)

if qty<=5 then

checkdays="days with 1 to 5 orders"

elseif qty<=10 then

checkdays= "days with 6 to 10 orders"

elseif qty<=20 then

checkdays= "days with 11 to 20 orders"

else

checkdays ="days with >20 orders"

end if

end function

then change load script

Orders:

LOAD order,

date,

article

FROM

C:\test.xls

(biff, embedded labels, table is Sheet1$);

Summary:

Left Join(Orders)

Load date,

checkdays(count(distinct order)) as DayType

resident Orders group by date;

Re: group by or cluster?

cscherer — Fri, 17 Aug 2012 12:18:43 GMT

this works, too. thanks a lot!