Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kohli
Creator II
Creator II

Count the records horizontally in a table..

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

            

9 Replies
pradosh_thakur
Master II
Master II

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
]
;
Learning never stops.
tresesco
MVP
MVP

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
Creator II
Creator II
Author

Not working Properly

pradosh_thakur
Master II
Master II

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

Learning never stops.
Kohli
Creator II
Creator II
Author

I want output like

UID, Total Read, Total Unread

XXX, Number, Number

Kohli
Creator II
Creator II
Author

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
Master II
Master II

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 ?

Learning never stops.
Kohli
Creator II
Creator II
Author

please find below my xl data file

Kohli
Creator II
Creator II
Author

please find below my xl data file