topic Re: Adding a column to a resident table in QlikView

Adding a column to a resident table

Fri, 10 Oct 2014 04:23:05 GMT

Hi,

I have a table loaded like so:

myTable:

load

id,field1

From...

then I left join it with another table such as

left join (myTable) load

id,field2

from ...

What I need now is to add a new key field to myTable :

field1&field2

adding this to the left join statement above fails. What is the best solution to do this for large data files (I would like to avoid re-loading a new table from resident and dropping the original table)

Re: Adding a column to a resident table

jyothish8807 — Fri, 10 Oct 2014 04:28:14 GMT

Hi Denis,

Try like this:

myTable:

load

id,field1

From...

left join (myTable)

load

id,field2

from ...

Tab2:

Load

field1&field2 as New field

resident myTable;

Regards

Re: Adding a column to a resident table

tresB — Fri, 10 Oct 2014 04:33:47 GMT

Try like:

myTable:

load

id,field1

From...;

left join (myTable) load

id,field2

from ...;

DesiredTable:

Load

field1&field2 as NewField

Resident myTable;

Drop Table myTable;

Re: Adding a column to a resident table

Vegar — Fri, 10 Oct 2014 05:07:25 GMT

I world go with resesco's solution. I would do a change to the key field

field1&field2 as key

I would make sure that the unique field1 and field2 combination gets an unique key.

Imagine the following

Field1 = 1 and Field2 = 12. --> Key = 112

Field1 = 11 and Field2 = 2 --> Key = 112

I tend to put pipe between each field in a composite key to avoid this problem.

field1 & '|' &field2 as key

I even do some kind of autonumber on the field to avoid long strings.

E.g. autonumber(field1 & '|' &field2) as key

Re: Adding a column to a resident table

ramasaisaksoft — Fri, 10 Oct 2014 05:31:22 GMT

Hi Dennis,

why don't you try like below

Load *,

filed1 &'_'& field2 as field3;

Actually we generally used this type of scenario for removing the Synthetic keys in table

i hope it will be helpful to u .

Re: Adding a column to a resident table

jonathandienst — Fri, 10 Oct 2014 05:41:12 GMT

If the you are only getting one or two fields from table2, then use a mapping to add it to table1, like this:

MapField2:

Mapping LOAD id, field2 from ...

myTable:

load

id,

field1,

ApplyMap('MapField2', id) As field2,

field1 & ApplyMap('MapField2', id) As field3

From...

(If there is more than one field, you can concatenate them into a single mapping or build multiple mapping tables; testing would indicate which is better in your case.)

HTH

Jonathan