If field value within range show a category name on table

[Marcushenriquesk]

I have a table on a sheet I am developing and the columns of that table are 'ACC_ID' and associated with the 'ACC_ID' field are 'Panel_ids.' What I want as a second column is a expression that states something along the lines of:

If 'Panel_ids' are within a range for the ACC_ID then show a string/variable that consolidates those panel_ids into one name.

For Example, for ACC_ID '1234' if Panel_ids >= 1 and Panel_ids <=3 output: 'Group name 1' or if Panel_ids >= 4 and Panel_ids <=6 output: 'Group_name 2'

How do i got about doing this cant find a solution anywhere.

[MarcoWedel]

Hi,

I'm not quite sure about your requirements.
Can you please elaborate maybe with some lines of the table containing ACC_ID and Panel_ids?

What if there are Panel_ids from different groups associated with the same ACC_ID?

In you example, what result would you expect if ACC_ID 1234 had occurrences with Panel_ids 2 and 5 ?

thanks for clarifying
Marco

[Marcushenriquesk]

so our system is pretty precise and if a ACC_ID has mutiple panel_id's all those panel ids would fall under a category. For example, lets say panel ids  1, 2 all fall under a category we call Group 1 and 3 ,4 are group 2. so if ACC_Id 1 has panel_ids 1 associated it would fall under a name called group 1 and if ACC_ID 2 has panels_ids 1, 2 it would also be in group 1 but if ACC_ID has panel_id 3 then it would fall under group 2. How i want this to work is i have a table that has a column for ACC_ID and a ACC_ID can have mutiple panel_ids meaning that duplicate ACC_IDs can be shown if i put Panel_id as another column. The go around is that if ACC_ID has panels_ids within this range then the column next to it labeled (panel Group) will show a string name for a range of those panel_ids. 

[MarcoWedel · Accepted solution]

Hi,

I think you can either calculate an additional Group field in the load script or use an expression in a straight table.

Either result would look like this:

Given this data:

A straight table expression using ACC_ID as dimension would be:

Only(If(Panel_ids <=  3, 'Group_name 1',
If(Panel_ids <=  6, 'Group_name 2',
If(Panel_ids <=  9, 'Group_name 3',
If(Panel_ids <= 12, 'Group_name 4',
If(Panel_ids <= 15, 'Group_name 5'
  ))))))

Whereas creating the Group field during load could be done like:

table1:
LOAD *,
     If(Panel_ids <=  3, 'Group_name 1',
     If(Panel_ids <=  6, 'Group_name 2',
     If(Panel_ids <=  9, 'Group_name 3',
     If(Panel_ids <= 12, 'Group_name 4',
     If(Panel_ids <= 15, 'Group_name 5'
       )))))			as Group;
LOAD ACC_ID,
     Panel_ids+IterNo()	as Panel_ids
While IterNo()<=3;
LOAD 1233+RecNo()		as ACC_ID,
     (RecNo()-1)*3		as Panel_ids
AutoGenerate 5;

hope this helps

Marco

[Marcushenriquesk]

thank you!

[MarcoWedel]

you're welcome