Data sub selection in Table/Straight table/Pivot Table

[#1]

Hi,

I am trying to build a couple of simple tables and I am looking for help/confirmation. Lets say you have

a set of data with 100 rows, and 10 columns. In column 2 there will be some blank values but that is

fine as you want to identify those columns.

I create a table box and add all the columns as I want to see those values without any calculations

performed against them. However, I do want to only see a subset of the data as I only want to see data

where column 2 has no value, null.

Question 1: What is the best way to achieve this? ( Essentially the equivalent of the where clause in

SQL statements )

Question 2: Is the same method applicable to Pivot and Straight tables?

Many Thanks

Graham

[jonathandienst]

Graham

you cannot normally select NULL values in tables or charts.

What I normally do in this sort of case is set the null values to something like "MISSING", or "UNKNOWN" in the load script. Then they can be selected like any other value.

Use something like

LOAD

...

     If(Len(field) = 0, 'UNKNOWN', field) As field

     ... or ...

     If(IsNull(field), 'UNKNOWN', field) As field

..

Hope that helps

Jonathan

[#3]

Hi Jonathan,

thank you for your response, this is exactly the approach I have take for the moment and have modified my load script accordingly.

so lets say that you do have the value 'UNKNOWN' in your dataset where the null value is in column 2. Now when i put that into my table box/straight table/pivot table how can that be defaulted to only that specific set of data without the user having to click anything or indeed being able to undo that initial selection criteria, i.e. all data where column 2 = 'UNKNOWN'? Is it something like a condition statement on the object, or does every dimension included have to be calculated and include a restrictive statement based on the 'UNKNOWN' value. In effect have a pre-filtered dataset in the object.

Many Thanks

Graham

[jonathandienst · Accepted solution]

Graham

You have several options here. For a table box, you could apply a selection (eg an action on opening the sheet). For a straight table, you could use a set expression for the chart expressions (eg soemthing like Sum({<field={'UNKNOWN'}>} value)). You don't need to use calculated dimensions, but you would need the set expression on each chart expression.

Regards

Jonathan

[ssc08081985]

I want to use a similar condition but with less than, something like :

{<Yearmm <  {'201804'} >}

Though 

{<Yearmm =  {'201804'} >} is working but the statement above is not.