Count Function in Script

[markgraham123]

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;

[MK_QSL]

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

[markgraham123]

Hi Manish,

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

[MK_QSL]

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

[maxgro]

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

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;

[markgraham123]

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?????

[maxgro]

                                   group by D2D3

is the same as        group by D2, D3

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

[markgraham123]

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.