Count the records horizontally in a table..

[Kohli]

I have table like..

UID,      Read,            Unread

Hari,   Science,      Social

            Hindi,           Maths

            English,      Environment

                                 Geography

                                  Statistics

Teja, Statistis,    Maths

          Hindi,           English

        kanada,       Geography

                               Nuerology

                                Physics

                               GK

I need to UID wise count  total read and total unread

Ex: UID       Count 0f Read,        Count of Unread

     Hari,     3,  5

     Teja,     3, 6

Please suggest me

[pradosh_thakur]

load UID,sum(if(len(trim(Read))>0,1,0)) as [Count 0f Read] , sum(if(len(trim(Unread))>0,1,0)) as  [Count 0f unRead] 
Group by UID;

LOAD * inline [
UID,      Read,            Unread,
Hari,   Science,      Social

  Hari,          Hindi,           Maths

 Hari,           English,      Environment

Hari, ,                                 Geography

 Hari, ,                                 Statistics

Teja, Statistis,    Maths

 Teja,         Hindi,           English

 Teja,      kanada,       Geography

 Teja, ,                             Nuerology

Teja , ,                                Physics

Teja, ,                               GK
]
;

[tresesco]

Simple Count() should work here. Could you share a sample app with may be inline data or explain a bit more, where and how exactly you are trying to achieve this?

[Kohli]

Not working Properly

[pradosh_thakur]

Did you run the script i posted @Kohli . It's matching.

[Kohli]

I want output like

UID, Total Read, Total Unread

XXX, Number, Number

[Kohli]

The Data available in XL file as am posted format.  column order is UID, Read, Unread. for one UID multiple read and unread rows are there.

[pradosh_thakur]

I guess that is what you are getting with the script i have posted. Can you tell what you are getting with the sample code i have posted and what is wrong ?

[Kohli]

please find below my xl data file

[Kohli]

please find below my xl data file