Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
@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;
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))
@imrasyed do you want new column to be calculated at frond end or in load script?
@Kushal_Chawda in load script.
Can we do it in Load script?
@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;
@Kushal_Chawda awesome man.
Thanks a lot.
Thanks Ranjith
Your solution worked in the front end.