topic Add a new column to a loaded table in QlikView

Add a new column to a loaded table

Wed, 24 Mar 2010 15:42:01 GMT

I am loading data in tables from QVD files and I want to add a new column, which will be a concatenation of two columns in the same table.

Can someone suggest how to accomplish this?

SET LoadTables='ABC','XYZ','PQR';

FOR EACH Table in $(LoadTables)

$(Table): LOAD * FROM $(Table).qvd (qvd);

I want to add a new column in first table (ABC).

Add a new column to a loaded table

vgutkovsky — Wed, 24 Mar 2010 15:49:25 GMT

Well, since you are doing this with FOR loop iterations, I don't think it's possible (unless of course you want to do the same thing for each table). But if you had just one table, ABC for example, you can do it with a preceding load like this:

ABC:
LOAD
*,
FieldA & FieldB as Field C
;
LOAD
*
FROM ABC.qvd (qvd);

Regards,

Add a new column to a loaded table

Wed, 24 Mar 2010 15:55:12 GMT

Vlad,

Thanks for replying.

I want to add the column in one of the tables only (say ABC) after the for loop.

After the loop exits, I know that I have a table loaded with the name ABC. Is there a way to do it in an already loaded table?

Add a new column to a loaded table

vgutkovsky — Wed, 24 Mar 2010 16:15:49 GMT

Sure. You can do it like this (after the for loop).

ABC2:
LOAD
*,
FieldA & FieldB as FieldC
RESIDENT ABC;

drop table ABC;
rename table ABC2 to ABC;

Regards,

Add a new column to a loaded table

johnw — Wed, 24 Mar 2010 19:52:35 GMT

If the table has a unique key, you can save some memory and possibly time by not creating a temporary duplicate table:

LEFT JOIN ([ABC])
LOAD
"Unique Key of ABC"
,FieldA & FieldB as FieldC
RESIDENT ABC
;

Also, if you go with the duplicate table approach, you'll probably need to do a NOCONCATENATE LOAD.

Add a new column to a loaded table

Tue, 06 Apr 2010 22:04:40 GMT

If ABC doesn't have a unique key, you can do this:

LEFT JOIN ([ABC])

LOAD FieldA

, FieldB

, FieldA & FieldB AS FieldC

RESIDENT ABC

GROUP BY FieldA, FieldB;

Add a new column to a loaded table

blaise — Tue, 06 Apr 2010 23:33:24 GMT

ddonia: may I ask why u use a group by clause in your example?

Add a new column to a loaded table

johnw — Wed, 07 Apr 2010 00:06:18 GMT


blaise wrote:ddonia: may I ask why u use a group by clause in your example?

If you don't do the group by, you can get create duplicate rows in the left join. Consider this table. The unique key of Row has been added merely for illustrative purposes. We won't be using it directly.

Row, FieldA, FieldB
1, X, Y
2, X, Y

If you do the left join without the group by, Row 1 will match both Row 1 and Row 2. Row 2 will match both Row 1 and Row 2. You end up with this:

Row, FieldA, FieldB, FieldC
1, X, Y, XY
1, X, Y, XY
2, X, Y, XY
2, X, Y, XY

That almost certainly isn't what you want. I make this or similar join mistakes with alarming frequency even though I should know better by now. At least I recognize the symptoms a lot faster these days, "Oh, messed up my join". One way I check for it is that most of my tables DO have a unique key. If that key is only in that one table, and a count of that key is different from a count distinct of that key, I know I messed up my table. You can also sometimes watch it happen in the log, with a table increasing in rows when it shouldn't be.

The group by prevents this from happening, because it will only join one copy of the (X, Y, XY) row, giving you what you wanted:

Row, FieldA, FieldB, FieldC
1, X, Y, XY
2, X, Y, XY