Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have some data that looks like this;
Desired output;
Cost type | Amount | ActFlg |
Actual Cost Labor | 175,054.00 | 1 |
Actual Cost Travel | 0 | 0 |
Projected Cost Labor | 12,144.00 | 0 |
Projected Cost Travel | 0 | 0 |
Actual Cost Travel | 100,000.00 | 1 |
Obligated Cost Travel | 0 | 0 |
Actual Cost Labor | 30,058.27 | 1 |
Projected Cost Labor | 0 | 0 |
I want to generate a column like 'ActFlg' possibly using Exists since I'm evaluating the current row vs. previous.
Here is what I have but its only generating 0 values.
If(Exists([Cost Type]='Actual*'), 1, 0) AS ActFlg,
Please help!
I ended up using this instead, but the Like was the missing link Thanks!
If([Cost Type] like 'Actual*' AND Amount > 0, 1, 0) AS ActFlg,
I haven't understood why you flag the very first record line.
But I assume you want something like
If(Exists([Cost Type]) and [Cost Type] like 'Actual*', 1, 0) AS ActFlg,
I forgot to add the other condition AND Amount > 0 in my posting
If(Exists(Amount > 0 And [Cost Type] like 'Actual*'), 1, 0) AS ActFlg,
I will try this Thanks!
In its one argument syntax, exists takes just a field name, no expression.
I think you need to use
If(Exists([CostType]) and Amount > 0 And [Cost Type] like 'Actual*', 1, 0) AS ActFlg,
I tried this but only Zeros...the operator Like is missing but Exists won't allow it?
If(Amount > 0 And Exists([Cost Type],'Actual*'), 1, 0) AS ActFlg,
output should be:
Costype | Amount | ActFlg | |
Actual Cost Labor | 175,054.00 | 1 | |
Actual Cost Travel | 0 | Not > 0 | |
Projected Cost Labor | 12,144.00 | 0 | |
Projected Cost Travel | 0 | ||
Actual Cost Travel | 100,000.00 | 1 | |
Obligated Cost Travel | 0 | ||
Actual Cost Labor | 30,058.27 | 1 | |
Projected Cost Labor | 0 |
I ended up using this instead, but the Like was the missing link Thanks!
If([Cost Type] like 'Actual*' AND Amount > 0, 1, 0) AS ActFlg,
I am trying to do this from 2 tables in my DB. So for example I'm trying:
userconf:
load userid,
city,
title,
(0) AS partnercheck;
SQL select userid,
city,
title,
from table;
This creates a field in userconf called partnercheck and sets the default value to 0. I want to now check another table in the DB to see if userid exists, and if it does, set the value of partnercheck to 1.
Any help is appreciated.
Thanks in advance