show row only if no date

[fgirardin]

Hello,

I'm working on a table containing multiple articles with ItemN°, price, start and end date for each price

My goal is to show ONLY rows where the END DATE is Null (meaning that the price is current as it has no end date)

Table:

ItemN°     Price     Start Date     End Date

001          10          1.1.2015       1.2.2015

002          15          1.1.2015          -

In this example I'd like to see only the row that contains Item 002

Tried to use a IF condition on the End Date Field but I don't get the result I want

Thanks for your help

[Kushal_Chawda]

Create the Flag in script like below

Data:

LOAD *,

        if(len(trim([End Date]))=0,1,0) as EndDateFlag

......

FROM Table;

If you want to restrict from back end then use WHERE

New:

noconcatenate

LOAD *

resident Data

where EndDateFlag=1;

If you want to restrict from front end then use expression like

=Sum({<EndDateFlag={1}>}Value)

[santiago_respane]

Hi Fabien,

Maybe you can achieve this with a set analysis expression:

=SUM({<EndDateFlag={'*'}-{''}>} SomeField)

Please let me know if this helps.

Kind regards,

[tamilarasu]

Fabien,

You can either use Isnull(Field) or Len(Trim(Field)) option. For load script try something like below,

Load *

From source where Len(Trim([End Date])) =0

If you want front end solution, try something like below.

= If(Len(Trim([End Date]))=0, Expression1)

Like wise add the if statement to all your expressions.

[fgirardin]

Thanks all for your answers.

I can't use the script version as I need to load all the datas

I have a problem adding a statement " =Sum({<EndDateFlag={1}>}Value) " or " = If(Len(Trim([End Date]))=0, Expression1) " as the table I'm working is:

ItemN°     Price   Price Type           Start Date      End Date

001           10            PA                  1.1.2015       1.2.2015

002           15            PA                  1.1.2015          -

I have 2 similar tables side by side, one showing Price Type "PA" and the other "PV", so I can check datas for both values

Dimension for these tables are:

ItemN°  -  Price  -  Price Type  -  Start Date  -  End Date

Expressions are:

Count({$<PRICETYPE={"PA"}>}PRICETYPE)

for the first table

and

Count({$<PRICETYPE={"PV"}>}PRICETYPE)

for the second table

I tried several formating but I can't seem to be able to add the new arguments into my expression

(example)

If(Len(Trim([End Date]))=0, Count({$<PRICETYPE={"PV"}>}PRICETYPE))

Thanks for your help