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

[DestinedTale]

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? 

[DestinedTale]

can anyone help?

[mohamed_ahid · Accepted solution]

 Hello,

like this ?

Regards

[Brett_Bleess]

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

[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;

[simrankaur]

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]

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.