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: 
DestinedTale
Contributor II
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)
1 Solution

Accepted Solutions
mohamed_ahid
Partner - Specialist
Partner - Specialist

 Hello,

like this ?

Regards

View solution in original post

6 Replies
DestinedTale
Contributor II
Contributor II
Author

can anyone help?

mohamed_ahid
Partner - Specialist
Partner - Specialist

 Hello,

like this ?

Regards

Brett_Bleess
Former Employee
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.
ramasaisaksoft

Hi,

please check below example script and output.

T1:
LOAD
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;

-1.JPG

simrankaur
Contributor III
Contributor III

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

NoConcatenate
c:
load *
Resident b
where Flag =1;

 

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


ans:
load diff,
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.

nvijay5757
Creator
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.