Create table using Load statement utilizing condition IF statement

[Chandan24k]

I am trying to execute below Script but it's not working I need to use column PROD , ACT and BUSN from Table1 and create another Table Enrol based on below condition,  

IF PROD = 01A or 05A then load count of field ACT as ActiveCNT  and also for same condition load count of field BUSN as EnrolCNT. The table Enrol should be created which I will use again in subsequent script to concatenate with another table.

Tried below script but it's giving error :-

[Enrol]:
Load Today() as Date,
IF((PROD = '01A' OR PROD = '05A'),count(ACT),Null()) as ActiveCNT,
IF((PROD = '01A' OR PROD = '05A'),count(BUSN),Null()) as EnrolCNT
RESIDENT [Table1];

[eddie_wagt]

Hi, it is failing because you forgot to group by.

Table1:
LOAD * INLINE [PROD, ACT, BUSN
01A, A, A
05A, B, B
];



[Enrol]:
NoConcatenate
Load Today() as Date,
IF((PROD = '01A' OR PROD = '05A'),count(ACT),Null()) as ActiveCNT,
IF((PROD = '01A' OR PROD = '05A'),count(BUSN),Null()) as EnrolCNT
RESIDENT [Table1]
Group By PROD
;

drop table Table1;

 Try this.

Regards Eddie 

[Chandan24k · Accepted solution]

Thank you Eddie. It worked.