Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

yelin_nyu
New Contributor III

consolidate fields and use correct name

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.


1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

Re: consolidate fields and use correct name

What if you modify your SQL Select to:

AcctTbl:

SQL  SELECT   DISTINCT acct,

Status,

New_acct,

Name

FROM "ACCOUNT_MASTER"

where Status <> 1;

1 Reply
fosuzuki
Valued Contributor II

Re: consolidate fields and use correct name

What if you modify your SQL Select to:

AcctTbl:

SQL  SELECT   DISTINCT acct,

Status,

New_acct,

Name

FROM "ACCOUNT_MASTER"

where Status <> 1;

Community Browser