how to Load * Inline with field reference within range of values

[Marcushenriquesk]

So I have a table called Panels. It contains a list of panel_names based on Panel_id. For example, Panel_ID = 1 has Panel_name = 'First_Panel_Name.' I want to create a scalable structure so that instead of using individual Panel_ids for the conditions within our functions we can use a load * Inline Table. It would look something like this:

PanelType :
load * Inline [
PANEL_ID, PanelType

if(PANEL_ID >70001 and PANEL_ID < 70075, Oral Fluid

 if(PANEL_ID >65001 and PANEL_ID < 65018, Oral Fluid

];

however, It doesnt recognize the if condition and just makes the selection ' if(PANEL_ID >65001 and PANEL_ID < 65018' = 'Oral Fluid'

[MarcoWedel]

can you post how "It doesnt recognize the if condition and just makes the selection ' if(PANEL_ID >65001 and PANEL_ID < 65018' = 'Oral Fluid'"?

How do you use this table?

[Marcushenriquesk]

this table will be used for conditional filteres within the functions of the charts in our apps. For example instead of creating a function : ''Count( distinct  If(Panel_id >=0 and Panel_id <=10, ACC_ID)" we would use : "Count( Distinct if(PanelType = 'Oral Fluid', ACC_ID)"

[tealowk]

Hi @Marcushenriquesk,

IMHO you don't need the PanelType table.

In the LOAD statement of your main table, just define this additional field:

LOAD *,

if(PANEL_ID >70001 and PANEL_ID < 70075, Oral Fluid,  if(PANEL_ID >65001 and PANEL_ID < 65018, Oral Fluid, 'Other') AS PanelType

FROM mySource;

[tealowk]

These expressions should be replaced with the corresponding set analysis expressions.

Old: Count( distinct  If(Panel_id >=0 and Panel_id <=10, ACC_ID)

New: Count( {<Panel_id={">=0 <=10"}>} distinct  ACC_ID)

Old: Count( Distinct if(PanelType = 'Oral Fluid', ACC_ID)

New: Count( {<PanelType={'Oral Fluid'}>} Distinct  ACC_ID)