Expression help

[apthansh]

HI Team -

I have data set as below.

ID1   Status         Date

1      Owned       8/10/2017

1      Leased      8/25/2017

2      Leased      9/10/2017

Here if ID1 is both Owned and Leased the I want to ignore the dates and if ID1 is Leased I want to show date.

If I put ID and Date in table box inly ID 2 should show date and ID should show blank for date.Is this doable ?

Thank yiu much.

[vishsaggi]

Something like this?

Use a straight table with Dim: ID1

                                expression: = IF(Count(Status) > 1 AND Status = 'Leased', 0, DateValue)

[vishsaggi]

Or This if you want to add the fields in your Table Box.

Table1:

LOAD *  INLINE [

ID1,   Status ,        DateValue

1  ,    Owned ,      8/10/2017

1  ,    Leased,      8/25/2017

2  ,    Leased,      9/10/2017

];

INNER JOIN (Table1)

LOAD ID1,

IF(Count(Status) > 1, '-', 1) As NewCounter

Resident Table1

Group By ID1;

NoConcatenate

Final:

LOAD ID1, Status, DateValue, IF(NewCounter = 1, DateValue) AS NewDate

Resident Table1;

Drop Table Table1;

[apthansh]

Can you please share the qvw ? I am not getting the same output.

[vishsaggi]

Check attached.