Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can i use many different expressions/functions or conditions in one dimension/ measure without getting a blank on my table or chart?
for example
=if(column1='OP', column2)
=Pick(wildmatch(column_name,'*Medic*','*Consult*', '*Proc*'),'MEDICATION','CONSULTATION','PROCEDURE')
can you post sample data and expected result?
your first expression will return a NULL when column1 <> 'OP'
if you dont want those rows, just uncheck the option to include NULLS for that dimension field.
You can nest many functions in a measure without issue, you just need to be careful with nesting of brackets and commas and the like.
If you have a calculation in a dimension, you always need to wrap this in an aggr function, so if you wanted to show the value of column2 if column1 is OP you would need to have:
=aggr(maxstring(if(column1='OP',column2,column1)), column1, column2)
This is known as a calculated dimensions, but calculated dimensions are generally a bad idea. You would be much better off creating a third column in the load script and then using that as the dimension, like this:
LOAD
column1,
column2,
if(column1 = 'OP', column2, column1) as column3,
etc.
Always, the more work you can do in the load script the better your application will be.
As @edwin says, sharing more about your data and what you expect to happen will help us help you find a solution.
Steve
I have a column with several strings with i want to use Pick(wildmatch( ) ) function to replace the common strings with one category name as follows
=Pick(wildmatch(column2,'*Medic*','*Consult*', '*Proc*'),'MEDICATION','CONSULTATION','PROCEDURE')
but also I want to restruct the same column to show only strings that adhere to a condition applied in another column as follows:
=if(column1='OP', column2)
You may just be able to nest the two expressions then:
=if(column1='OP', Pick(wildmatch(column2,'*Medic*','*Consult*', '*Proc*'), 'MEDICATION','CONSULTATION','PROCEDURE'))
Steve