Announcements
cancel
Showing results for
Did you mean:
Contributor II

## To find the first negative value and display it as -1

Hi i have a qvd that do a running number of formula where A-B.

Eventually i want to display the value -1 for the first negative value from A-B.

How can i achieve this?

Labels (2)

• ### QLIKVIEW Script

1 Solution

Accepted Solutions
Partner - Specialist

Hello,

like this ?

Regards

6 Replies
Contributor II
Author

can anyone help?

Partner - Specialist

Hello,

like this ?

Regards

Former Employee

Did Mohamed's sample app get you what you needed?  If it did, do not forget to come back to the thread and use the Accept as Solution button on his post to give him credit for the help and let others know it did work.  If you are still working on things, leave an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Master

Hi,

please check below example script and output.

T1:
AutoNumber(Rowno())as RowLine

FROM
[C:\Users\Desktop\QlikView Straight table files.xlsx]
(ooxml, embedded labels, table is Sheet1);

T2:
Load RowLine,if( RowLine-2<=0,-1,(RowLine-2)) as Result Resident T1;

Contributor III

b:
rowno() as row,
if (peek(diff)>0, if(diff<0,1,0),0) as Flag
Resident a;

NoConcatenate
c:
Resident b
where Flag =1;

let vnum= Peek('diff',0,c);

ans:
if(diff= \$(vnum), -1 , diff) as actual_diff
resident b;
drop table c,b;

This way you will get 1 as flag for 1st occurence of negative difference.

Creator

Hello,

your logic is Good. can we identify first negative value more than one column. i mean 15 to 20 columns.

Thanks. sample data attached.

Community Browser