- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can anyone help?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
like this ?
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.