Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return first data for last negative value period

Hello,

I need return first Date for last negative Value period.

Table:

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/5/2014, 6

1, 1/6/2014, 4

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

Need add field with marked 1/7/2014 for ID=1

Thanks for advice!

BR

Ilgmar

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

Used some sorting in the load script to pick up the first negative value in the latest continous block of negative values:

Capture.PNG.png

List box expression:

=FirstSortedValue( total <ID> Value,if(Flag=1,-RecordNumber))

Chart expression:

=FirstSortedValue(TOTAL <ID> Value,if(Flag=1,-RecordNumber))

Load Script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Temp:

load

*,

RecNo() as RecordNumber,

if(Value < 0 and Previous(Value) < 0 , RecNo()-1,RecNo()) as [12Negative]

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/5/2014, 6

1, 1/6/2014, 4

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

NoConcatenate

LOAD

  *,

  if([12Negative]=Previous([12Negative]),1,0) as Flag

Resident Temp

Order by RecordNumber desc;

drop table Temp;

View solution in original post

8 Replies
anbu1984
Master III
Master III

How do you define last negative value? Do you select the row followed by last positive value(Here 4)?

Not applicable
Author

not quite, becouse some ID can start with negative value and stay negative whole period, then it does not work.

anbu1984
Master III
Master III

Then how do you identify last negative value?

Not applicable
Author

Definition:

if last report date ID value is negative, we need return data, when started last negative period. (In real, when it became as bad debitor)

Is it clear?

Not applicable
Author

As I understand correctly, you need to add a flag to your table.

Your definition seems to be wrong what you need the result on the test data described above.

Please post some more data with test cases to understand the requirement clearly.

JonnyPoole
Employee
Employee

Used some sorting in the load script to pick up the first negative value in the latest continous block of negative values:

Capture.PNG.png

List box expression:

=FirstSortedValue( total <ID> Value,if(Flag=1,-RecordNumber))

Chart expression:

=FirstSortedValue(TOTAL <ID> Value,if(Flag=1,-RecordNumber))

Load Script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Temp:

load

*,

RecNo() as RecordNumber,

if(Value < 0 and Previous(Value) < 0 , RecNo()-1,RecNo()) as [12Negative]

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/5/2014, 6

1, 1/6/2014, 4

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

NoConcatenate

LOAD

  *,

  if([12Negative]=Previous([12Negative]),1,0) as Flag

Resident Temp

Order by RecordNumber desc;

drop table Temp;

anbu1984
Master III
Master III

So if your data doesn't have positive values, then you need Date - 1/1/2014?

Table:

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

Not applicable
Author

Thanks for advice!