consolidate fields and use correct name

yelin_nyu — Mon, 01 Oct 2012 15:40:05 GMT

Hi, stores are being re-organized if status code is 1, new_acct represents the new acct # they are going to take on (ignore all other status code, it's not very important here)

acct Status New_acct Name

123 1 111 AAA

456 0 333 BBB

789 1 111 CCC

111 4 111 XXX

So acct 123 and 789 are going to be combined into 111 acct.

This is what i did in script

AcctTbl:

SQL SELECT DISTINCT "acct" AS original_acct,

Status,

Name,

New_acct,

case status

when '1' then New_acct

else acct

END as acct

FROM "ACCOUNT_MASTER";

I want to get this:

acct Status New_acct Name

456 0 333 BBB

111 4 111 XXX

but i got this

acct Status New_acct Name

111 1 111 AAA

456 0 333 BBB

111 1 111 CCC

111 4 111 XXX

Problem with what i got is acct 111 will have 3 names when it really should be 1 name. please help.

Re: consolidate fields and use correct name

fosuzuki — Mon, 01 Oct 2012 19:46:50 GMT

What if you modify your SQL Select to:

AcctTbl:

SQL SELECT DISTINCT acct,

Status,

New_acct,

Name

FROM "ACCOUNT_MASTER"

where Status <> 1;

topic Re: consolidate fields and use correct name in QlikView

consolidate fields and use correct name

Re: consolidate fields and use correct name