Add a new column to a loaded table

[#1]

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);

NEXT

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

[vgutkovsky]

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,

[#3]

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?

[vgutkovsky]

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,

[johnw]

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.

[#6]

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;

[blaise]

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

[johnw]

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