Column based on the other Column Values

[imrasyed]

Hi Experts,

I have

TableA:

Rid Pid State

A       1       Com

A       2        NonCom

B      1       Com

C      1        NonCom

I want to create a new column based on the values in the state Column 

i.e if for a Rid   State  is both Com and NonCom then NonCom

                          else state is only Com then Com

                         else state is only NonCom the NonCom

TableResult:

Rid         OverallState

A             NonCom

B            Com

C            NonCom

How Can i achieve this?

Truly Appreciate your help.

[dplr-rn · Accepted solution]

are these the only options or are there more permutations and combinations?

roughly you could do a measure on a table like so.

// if more than 1 State mark it as noncom and if not just show the state

if(count(distinct State)>1

         ,'NonCom' 

         ,Only(State))

[Kushal_Chawda]

@imrasyed  do you want  new column to be calculated at frond end or in load script?

[imrasyed]

@Kushal_Chawda  in load script.

[imrasyed]

Can we do it in Load script?

[Kushal_Chawda · Accepted solution]

@imrasyed  try below

TableA:
LOAD * Inline [
Rid, Pid ,State
A ,      1 ,      Com
A ,      2 ,       NonCom
B ,     1 ,      Com
C ,     1 ,       NonCom ];

Left Join
LOAD Rid,
     Concat(State,'|') as Concat_State
Resident TableA
Group by Rid;

Final:
NoConcatenate
LOAD *,
     if(index(Concat_State,'NonCom'),'NonCom',State) as Final_State
Resident TableA;

DROP Table TableA;

DROP Field Concat_State;

[imrasyed]

@Kushal_Chawda  awesome man.

Thanks a lot.

[imrasyed]

Thanks Ranjith

Your solution worked in the front end.