topic Re: How to use Exist() for generating Flag value 0/1 in script in QlikView

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

Wed, 26 Sep 2012 16:05:16 GMT

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!

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

swuehl — Wed, 26 Sep 2012 16:14:32 GMT

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,

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

Wed, 26 Sep 2012 16:17:37 GMT

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!

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

swuehl — Wed, 26 Sep 2012 16:22:30 GMT

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,

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

Wed, 26 Sep 2012 16:28:53 GMT

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

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

Wed, 26 Sep 2012 16:31:53 GMT

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,

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

Fri, 01 Nov 2013 10:45:46 GMT

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