topic join four columns from one table in QlikView

join four columns from one table

lorenzo_gibbo — Fri, 08 Apr 2011 15:54:28 GMT

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

join four columns from one table

pat_agen — Fri, 08 Apr 2011 16:02:44 GMT

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

join four columns from one table

lorenzo_gibbo — Fri, 08 Apr 2011 16:19:04 GMT

Great!

just like I wanted

Thanks!

join four columns from one table

johnw — Fri, 08 Apr 2011 21:51:20 GMT

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;

join four columns from one table

lorenzo_gibbo — Sat, 09 Apr 2011 14:45:32 GMT

Thank you very very much!!!