topic Re: Count Function in Script in QlikView

Count Function in Script

markgraham123 — Wed, 22 Jul 2015 18:20:42 GMT

Hi,

I have a table where fields were as follows:

temp1:

Load D1, D2, D3, D4, D5...D10 from table;

I wanna perform count(D2) in script.

So, the script should be like...

temp2:

Load D1, D2, Count(D2), D3, D4, D5 from table Group by D1, D2 D3, D4, D5..D10;

Instead, Can i follow the approach...

temp1:

Load D1, D2, D3, D4, D5..D10 from table;

join

Load D2, Count(D2) from table Group by D2;

Re: Count Function in Script

MK_QSL — Wed, 22 Jul 2015 18:27:39 GMT

You can but the result would be different... For 2nd approach the group by will only with D2

Re: Count Function in Script

markgraham123 — Wed, 22 Jul 2015 18:33:12 GMT

Hi Manish,

ANy idea how can i approach to get count() for D2 through script.

Re: Count Function in Script

MK_QSL — Wed, 22 Jul 2015 18:38:37 GMT

If you just want to add TotalD2Count then you can follow as below

Temp:
Load D1,D2,D3,D4 From TableName;

Left Join (Temp)

Load Count(Distinct D2) as TotalD2 Resident Temp;

or Count(D2) if you don't want Distinct

But if you want D2 with respect to D2

Left Join(Temp)

Load D1, Count(D2) Resident Temp Group By D1;

You can add D3, D4, D5 similar manner but need to add them also in Group By

Re: Count Function in Script

maxgro — Wed, 22 Jul 2015 18:45:25 GMT

you can, but the result of the count depends on group by fields (see image)

zz:

load rowno() as id, * inline [

d1,d2,d3,d4,d5

1,1,1,1,1

2,2,2,2,2

3,3,3,3,3

4,4,4,4,4

1,1,1,1,1

11,1,11,11,11

];

left join (zz) load d2, count(d2) as countd2 resident zz group by d2;

left join (zz) load d1,d2,d3,d4,d5, count(d2) as count2d2 resident zz group by d1,d2,d3,d4,d5;

Re: Count Function in Script

markgraham123 — Wed, 22 Jul 2015 19:00:01 GMT

Hi Massimo,

I have a small question here.

If in the table ZZ, i'm doing D2&D3 as D2D3.

Do i have to use "D2D3" in group By?????

Else just D1, D2, D3, D4, D5?????

Re: Count Function in Script

maxgro — Wed, 22 Jul 2015 19:07:10 GMT

group by D2D3

is the same as group by D2, D3

is not the same as group by D1, D2, D3, D4, D5

Re: Count Function in Script

markgraham123 — Wed, 22 Jul 2015 19:23:43 GMT

HI Massimo,

I tried

Temp:
Load D1,D2,D3,D4, D1&D2 as New1 From TableName;

Left Join (Temp)

Load D1, D2, D3, D4, New1, Count( D2) as TotalD2 Resident Temp

Group By D1, D2, D3, D4, New1;

Its showing the wrong data.