Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
small question:
I have four columns of data in the same table.
SQL SELECT
TACO01,
TACO02,
TACO03,
TACO04
FROM mytable;
I would like to create a single column with all the data.
Example:
TACO01 | TACO02 | TACO03 | TACO04 |
12 | 15 | 18 | 30 |
AB | 18 | 24 | 32 |
C | C | 32 | 35 |
RESULT |
AB |
C |
12 |
15 |
18 |
24 |
30 |
32 |
35 |
Can someone help me?
Thanks
hi,
one way would be to read your table 4 times. either direct from your dbms or inside qlikview.
either
tableWithOneColumn:
load TACO01 as mySingleColumn;
SQL SELECT
TACO01
FROM mytable;
tableWithOneColumn:
concatenate load TACO02 as mySingleColumn;
SQL SELECT
TACO02
FROM mytable;
tableWithOneColumn:
concatenate load TACO03 as mySingleColumn;
SQL SELECT
TACO03
FROM mytable;
tableWithOneColumn:
concatenate load TACO04 as mySingleColumn;
SQL SELECT
TACO04
FROM mytable;
or if you just want the one hit against your original table
loadData:
SQL SELECT
TACO01,
TACO02,
TACO03,
TACO04
FROM mytable;
tableWithOneColumn:
load TACO01 as mySingleColumn
resident loadData;
tableWithOneColumn:
concatenate load TACO02 as mySingleColumn
resident loadData;
tableWithOneColumn:
concatenate load TACO03 as mySingleColumn
resident loadData;
tableWithOneColumn:
concatenate load TACO04 as mySingleColumn
resident loadData;
drop table loadData;
hope this helps
hi,
one way would be to read your table 4 times. either direct from your dbms or inside qlikview.
either
tableWithOneColumn:
load TACO01 as mySingleColumn;
SQL SELECT
TACO01
FROM mytable;
tableWithOneColumn:
concatenate load TACO02 as mySingleColumn;
SQL SELECT
TACO02
FROM mytable;
tableWithOneColumn:
concatenate load TACO03 as mySingleColumn;
SQL SELECT
TACO03
FROM mytable;
tableWithOneColumn:
concatenate load TACO04 as mySingleColumn;
SQL SELECT
TACO04
FROM mytable;
or if you just want the one hit against your original table
loadData:
SQL SELECT
TACO01,
TACO02,
TACO03,
TACO04
FROM mytable;
tableWithOneColumn:
load TACO01 as mySingleColumn
resident loadData;
tableWithOneColumn:
concatenate load TACO02 as mySingleColumn
resident loadData;
tableWithOneColumn:
concatenate load TACO03 as mySingleColumn
resident loadData;
tableWithOneColumn:
concatenate load TACO04 as mySingleColumn
resident loadData;
drop table loadData;
hope this helps
Great!
just like I wanted
Thanks!
A crosstable load allows you to do this more simply. I think this would do it:
CROSSTABLE (Name,Result)
LOAD recno() as Recno,*;
SQL SELECT TAC001, TAC002, TAC003, TAC004
FROM mytable;
DROP FIELDS Recno, Name;
Or you might need to do it in two steps:
Raw:
SQL SELECT TACO01, TACO02, TACO03, TACO04
FROM mytable;
New:
CROSSTABLE (Name,Result)
LOAD recno() as Recno,*
RESIDENT Raw;
DROP FIELDS Recno, Name;
DROP TABLE Raw;
Though actually, in this specific case, you don't need all of what crossfield does for you, so another option is to "cheat" and use subfield() to turn columns into rows. It seems a little shorter, but I don't like how it has to repeat the field names. I'd probably stick with crosstable.
LOAD subfield(TAC001 & ',' & TAC002 & ',' & TAC003 & ',' & TAC004, ',') as Result;
SQL SELECT TAC001, TAC002, TAC003, TAC004
FROM mytable;
Thank you very very much!!!