Count while load

[peterkumar]

Hello,

My input data has Client Exec and Meeting Status.

Meeting Status can be complete or incomplete.

There may be multiple records per Client Exec and each record will have one status - Complete or Incomplete.

I need to create a data set with one record for each Client Exec that shows the name and the number of completed meetings as well as the number of incomplete meetings.

I have tried the code below, but it is not working correctly -

LOAD
  [Meeting Status],
   [Client Executive Name],
   If ([Meeting Status] = 'Completed',count([Meeting Status])) as Completed_Meetings,count([Meeting Status]) as Incomplete_Meetings

Resident PS17_DATA 
Group By
[Client Executive Name],
[Meeting Status]
;

I will greatly appreciate your suggestions.

Thanks

[sunny_talwar]

Try this:

LOAD  [Client Executive Name],
            Count(If ([Meeting Status] = 'Completed', [Meeting Status])) as Completed_Meetings,

            Count([Meeting Status]) as Incomplete_Meetings
Resident PS17_DATA 
Group By [Client Executive Name];

[peterkumar · Accepted solution]

Thanks Sunny.

You have led me to the right path.

LOAD

[Client Executive Name],
Count(If ([Meeting Status] = 'Completed', [Meeting Status])) as Completed_Meetings,
Count(If ([Meeting Status] = 'Incomplete',[Meeting Status])) as Incomplete_Meetings


Resident PS17_DATA 
Group By [Client Executive Name];

The second If prevents from counting the Completed as Incomplete.

Thanks again!

[sunny_talwar]

Great

Please close this thread by marking correct and helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny