Using Count in Load Script

[psenthilbtech]

Hi Team, 

Attached excel file (sample data)

I have a requirement  - where i have to count the number of cases as below in table format

Condition : If the caseclosed Date is Null , Its Ope

n else closed

Sample Output:

Status	Q1	Q2	Q3
Total Cases Received	21	5	1
Total Open Cases	12	3	0
Total Closed Cases	9	2	1

[Gabriel]

Hi,

Try this code

mp:
LOAD *,
IF(ISNULL(CaseClosedDate) OR CaseClosedDate='-' ,1) AS [CaseClose Count]
INLINE [
CaseNumber,CaseRaisedTime ,CaseRaised_Year,CaseRaised_Quarter,CaseClosedDate,CaseClosed_Month,CaseClosed_Quarter
0004-1-2020,43849.2960572917,2020,Q1,43920.862241088,Mar,Q1
0005-1-2020,43850.5665556713,2020,Q1,-,-,-
0006-1-2020,43851.3177413194,2020,Q1,-,-,-
0007-2-2020,43865.3019780093,2020,Q1,-,-,-
0008-2-2020,43873.4315972222,2020,Q1,-,-,-
0009-2-2020,43877.4362828704,2020,Q1,-,-,-
0010-2-2020,43879.2876637732,2020,Q1,43894.5457523148,Mar,Q1
0011-2-2020,43879.4460618056,2020,Q1,-,-,-
0012-2-2020,43879.4339795139,2020,Q1,-,-,-
0013-2-2020,43879.4139877315,2020,Q1,-,-,-
0014-2-2020,43880.4651107639,2020,Q1,-,-,-
0015-2-2020,43886.4879127314,2020,Q1,-,-,-
0016-3-2020,43892.5163929398,2020,Q1,-,-,-
0017-3-2020,43892.5237408565,2020,Q1,-,-,-
0018-3-2020,43893.5079774306,2020,Q1,-,-,-
0019-3-2020,43894.5328575231,2020,Q1,-,-,-
0020-3-2020,43894.3894677083,2020,Q1,-,-,-
0021-3-2020,43894.5568274306,2020,Q1,-,-,-
0022-3-2020,43895.4666107639,2020,Q1,-,-,-
0023-3-2020,43895.4753555556,2020,Q1,-,-,-
0024-3-2020,43905.3290984954,2020,Q1,-,-,-
0025-3-2020,43905.4551256944,2020,Q1,-,-,-
0026-3-2020,43921.3682516204,2020,Q1,-,-,-
0027-4-2020,43927.1719795139,2020,Q2,-,-,-
0028-4-2020,43928.6357613426,2020,Q2,-,-,-
0029-4-2020,43929.3623736111,2020,Q2,-,-,-
0030-4-2020,43934.9305427083,2020,Q2,-,-,-
];

And in UI you can have expression such as 

=COUNT({<[CaseClose Count] ={1}>}CaseClosedDate)

Let us know if it helps