Creating custom column based on 3 other colums

[Prasanthh60]

HI,

Please help me to create a custom colum based on 3 other colums.

1. if patient type is OP then custom colum should reflect OP.

2. If patient type is only IP and Sponsor is only blank and Net amount less than 1000 then OP others as IP.

basically, the custom colum should reflect as the below.

Patient Type	Sponsor	Net Amt	Custom colum
IP	Payer - Y	490	IP
IP		47	OP
IP		4200	IP
OP	Payer - Y	300	OP
OP		25	OP
OP		2100	OP

[Uzi2019]

Hi @Prasanthh60 

You can try below calculated column.

Custom new =

IF(Customer[Patient Type]="OP","OP", If(Customer[Patient Type]="IP" && Customer[Sponsor]=BLANK() && Customer[Net Amt]<1000,"OP","IP"))

I hope i answered your question!

[Prasanthh60]

Thank for your input. 

But I am getting the below error. can you please help.

[Uzi2019]

Hi @Prasanthh60 

share your formula also...
bcz by looking at error wont able to understand you formula mistake.

[Prasanthh60]

Thanks in advance.

custnew = IF('12#06#2024'[Patient Type]="OP","OP", IF('12#06#2024'[Patient Type]="IP" && '12#06#2024'[Primary Bill Sponsor]=BLANK() && '12#06#2024'[Net Amt]< 1500, "OP", "IP"))

[Uzi2019 · Accepted solution]

Hi @Prasanthh60 

Your net amount column is text please convert into interger / whole number.

select your net amount then convert to whole number.

[Prasanthh60]

It worked. Thank you so much.

[Uzi2019]

Hi @Prasanthh60 

Did u take column right not measure???

[Prasanthh60]

Yes sir.  Under Modeling - new column

[Uzi2019]

Hi @Prasanthh60 

error you are getting for type conversion it means your either of the column is text and comparing with numeric value<1000 or either of your column is numberi comparing with text value "OP" /"IP"..

Please check that..

I\

I hope it may resolved your issue!

[Joe_Barry]

Hi @Prasanthh60 

You are basically there with how you outlined your reuqirements.

CustomColumn =
IF(Table[Patient Type] = "IP" &&
Table[Sponsor]= BLANK() &&
Table[Net Amt] < 1000, "OP",
IF(Table[Patient Type] = "OP", "OP", "IP")

Hope this helps

Joe

[Prasanthh60]

Thanks joe for your input. But i am getting the same error as below.