topic Re: Get a single Row for an ID from multiple Rows in QlikView

Get a single Row for an ID from multiple Rows

microwin88x — Tue, 10 Feb 2015 15:19:03 GMT

Hello,

I have the following table as a script in QlikView:

ACCT_ID	FLAG_1	FLAG_2	FLAG_3
005	0	0	0
005	0	0	0
005	0	0	0
005	0	0	1
005	0	1	0
005	1	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!

Re: Get a single Row for an ID from multiple Rows

maxgro — Tue, 10 Feb 2015 15:21:37 GMT

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

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

group by ACCT_ID;

DROP Table t;

Re: Get a single Row for an ID from multiple Rows

jmmayoral3 — Tue, 10 Feb 2015 15:27:46 GMT

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>;