Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

I have some data that looks like this;

Desired output;

Cost typeAmountActFlg
Actual Cost Labor 175,054.00 1
Actual Cost Travel00
Projected Cost Labor   12,144.00 0
Projected Cost Travel00
Actual Cost Travel 100,000.00 1
Obligated Cost Travel00
Actual Cost Labor   30,058.27 1
Projected Cost Labor00

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!

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

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,

6 Replies
MVP
MVP

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

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,

Not applicable

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

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!

MVP
MVP

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

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,

Not applicable

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

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:

CostypeAmountActFlg
Actual Cost Labor 175,054.00 1
Actual Cost Travel0Not > 0
Projected Cost Labor   12,144.00 0
Projected Cost Travel0
Actual Cost Travel 100,000.00 1
Obligated Cost Travel0
Actual Cost Labor   30,058.27 1
Projected Cost Labor0
Not applicable

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

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,

Not applicable

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

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

Community Browser