Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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>;