Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
You can but the result would be different... For 2nd approach the group by will only with D2
Hi Manish,
ANy idea how can i approach to get count() for D2 through script.
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
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;
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?????
group by D2D3
is the same as group by D2, D3
is not the same as group by D1, D2, D3, D4, D5
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.