join four columns from one table

[lorenzo_gibbo]

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

[pat_agen · Accepted solution]

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

[lorenzo_gibbo]

Great!

just like I wanted

Thanks!

[johnw]

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;

[lorenzo_gibbo]

Thank you very very much!!!