Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
imrasyed
Partner - Creator II
Partner - Creator II

Column based on the other Column Values

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.

 

 

Labels (1)
2 Solutions

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

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))

View solution in original post

Kushal_Chawda

@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;

View solution in original post

7 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
Partner - Creator II
Partner - Creator II
Author

@Kushal_Chawda  in load script.

imrasyed
Partner - Creator II
Partner - Creator II
Author

Can we do it in Load script?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Kushal_Chawda

@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
Partner - Creator II
Partner - Creator II
Author

@Kushal_Chawda  awesome man.

Thanks a lot.

imrasyed
Partner - Creator II
Partner - Creator II
Author

Thanks Ranjith

Your solution worked in the front end.