topic Expression help in QlikView

Expression help

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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 — Wed, 23 Aug 2017 19:43:26 GMT

Something like this?

Use a straight table with Dim: ID1

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

vishsaggi — Wed, 23 Aug 2017 20:03:02 GMT

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 — Thu, 24 Aug 2017 18:31:48 GMT

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

vishsaggi — Thu, 24 Aug 2017 19:21:13 GMT

Check attached.