Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Return first data for last negative value period

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;

8 Replies
anbu1984
Honored Contributor III

Re: Return first data for last negative value period

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

Not applicable

Re: Return first data for last negative value period

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

anbu1984
Honored Contributor III

Re: Return first data for last negative value period

Then how do you identify last negative value?

Not applicable

Re: Return first data for last negative value period

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

Re: Return first data for last negative value period

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.

Employee
Employee

Re: Return first data for last negative value period

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
Honored Contributor III

Re: Return first data for last negative value period

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

Re: Return first data for last negative value period

Thanks for advice!

Community Browser