topic Re: flags in QlikView

flags

ravindraa — Tue, 13 May 2014 11:58:04 GMT

Hi all

we have one requirement in that requirement we have two files 1.file1

2. file2

in the file1 we have some fields :: contract, customer, business, colorcode , status, .............

in the file2 we have some fields :: contract, customer, business, colorcode , status,.................

status have 5 values :ACTIVE,

ACREATED

ENROLLED

CANCELLED

we need to count "contract" based on status wise for this we have wrote logic like this.

this is for one database:::

if(Status='ACTIVE',Count({<Status={ACTIVE}>}Contract),if(Status='CANCELLED',Count({<Status={CANCELLED}>}Contract),if(Status='ENROLLED',Count({<Status={ENROLLED}>}Contract),if(Status='NA',Count({<Status={NA}>}Contract),if(Status='CREATED',Count({<TAStatus={CREATED}>}TContract))))))

we need to count "contract" based on status wise for this we have wrote logic like this.

this is for second database:::

if(Status='ACTIVE',Count({<SAStatus={ACTIVE}>}SAContract),if(Status='CANCELLED',Count({<SAStatus={CANCELLED}>}SAContract),if(Status='ENROLLED',Count({<SAStatus={ENROLLED}>}SAContract),IF(Status='CREATED',0,IF(Status='NA',0)))))

using this we get the differentiated value from both 2 database

here difference value is ::

ACTIVE, =1500

ACREATED =10

ENROLLED =2

NA =6

CANCELLED =3

like this but here if we select 1500 value we need to see all contract list for active like wise for all status i think we need to provide flags for all status but i am not sure if that is right please give any suggestion how it is possible.

if any one know please give suggestions................ it is very urgent............

Re: flags

Tue, 13 May 2014 12:09:42 GMT

Do you have any transaction ID for each transaction.

Re: flags

ravindraa — Tue, 13 May 2014 12:51:38 GMT

here we don't have any transaction id's but every customer have one contract based on business.

Re: flags

Tue, 13 May 2014 12:59:13 GMT

Assuming you are trying to represent the above in a pie, try the below

Dimension

=if(Count({<Status={ACTIVE}>}Contract) >= 0, 'ACTIVE',

if(Count({<Status={ACREATED}>}Contract) >= 0, 'ACREATED',

if(Count({<Status={ENROLLED}>}Contract) >= 0, 'ENROLLED',

if(Count({<Status={NA}>}Contract) >= 0, 'NA',

if(Count({<Status={CANCELLED}>}Contract) >= 0, 'CANCELLED')))))

Expression

count(Contract)

Re: flags

Roop — Tue, 13 May 2014 13:02:22 GMT

I think that you are right in your supposition that it would be simplest for development to create a bunch of flags and do it in the load script with a statement like:

if (STATUS = 'ACTIVE',

) as Count_Active,

If(STATUS = 'CANCELLED',

) as Count_Cancel,

etc. for all others

This would allow you to create a report very quickly with Sum(Count_Cancel) and Sum(Count_Active) and to create percentage figures as well

Hope it helps

Re: flags

Tue, 13 May 2014 13:10:35 GMT

Create a Calculated dimension

IF(wildmatch(FIELDNAME, '*ACTIVE*', '*ACREATED*','*ENROLLED*','*NA*','*CANCELLED*'), FIELDNAME)

and use the expression as Count (distinct Contract).