Get a single Row for an ID from multiple Rows

[microwin88x]

Hello,

I have the following table as a script in QlikView:

ACCT_ID	FLAG_1	FLAG_2	FLAG_3	FLAG_4	FLAG_5
005	0	0	0	0	0
005	0	0	0	0	0
005	0	0	0	0	0
005	0	0	1	0	0
005	0	1	0	0	0
005	1	0	0	0	0

What I want is to get a single row for that ACCT_ID where if I have 0 and 1 values for a FLAG column, just keep the 1, if not the 0. In the case I mentioned, I should get something like this:

ACCT_ID	FLAG_1	FLAG_2	FLAG_3	FLAG_4	FLAG_5
005	1	1	1	0	0

Is there any way to do this by script?

Thank you!

[maxgro · Accepted solution]

with a group by ACCT_ID and max for all other fields (FLAG_n)

t:

LOAD ACCT_ID,

     FLAG_1,

     FLAG_2,

     FLAG_3,

     FLAG_4,

     FLAG_5

FROM

[http://community.qlik.com/thread/151476]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate load

  ACCT_ID,

     max(FLAG_1) as FLAG_1,

     max(FLAG_2) as FLAG_2,

     max(FLAG_3) as FLAG_3,

     max(FLAG_4) as FLAG_4,

     max(FLAG_5) as FLAG_5    

Resident

  t

group by ACCT_ID;

DROP Table t;

[jmmayoral3]

Try this

TableAux:

LOAD       //Precedent Load

          ACCT_ID,

          IF(FLAG1>0,1,0) AS FLAG1,

          IF(FLAG2>0,1,0) AS FLAG2,

          IF(FLAG3>0,1,0) AS FLAG3,

          IF(FLAG4>0,1,0) AS FLAG4,

          IF(FLAG5>0,1,0) AS FLAG5

;

LOAD

    ACCT_ID,

    SUM(FLAG1) AS FLAG1,

    SUM(FLAG2) AS FLAG2,

    SUM(FLAG3) AS FLAG3,

    SUM(FLAG4) AS FLAG4,

    SUM(FLAG5) AS FLAG5

RESIDENT <YouTableName>;