How to use Exist() for generating Flag value 0/1 in script

[#1]

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!

[swuehl]

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,

[#3]

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!

[swuehl]

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,

[#5]

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

[#6 · Accepted solution]

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,

[#7]

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