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

Adding a column to a concatenated table

hobanwashburne — Tue, 05 Aug 2014 14:22:47 GMT

I have two tables that I want to first concatenate, then add a Unique Key (via RowNo()) to the concatenated table. How do I force the concatenation first before adding the Key?

Re: Adding a column to a concatenated table

datanibbler — Tue, 05 Aug 2014 14:29:30 GMT

Hi Scott,

well, you need to compute the key as an additional column in the final table - so I guess you'll need a RESIDENT LOAD - put the two LOADs (for the concatenation) first and then load the resulting table RESIDENT and add the calculated key.

Re: Adding a column to a concatenated table

ashfaq_haseeb — Tue, 05 Aug 2014 14:32:06 GMT

Temp:

Load

From Table1;

concatenate

Load

From Table2;

Main

Load *,rownno() as Key;

resident Temp;

drop table Temp;

Regards

ASHFAQ

Re: Adding a column to a concatenated table

maxgro — Tue, 05 Aug 2014 14:36:44 GMT

// make 2 tables

test1:

load rand()*1000 as f1, rand()*100 as f2

AutoGenerate 1000;

test2:

load rand()*1000 as f1, rand()*100 as f3

AutoGenerate 1000;

// concat 2 tables

NoConcatenate load * Resident test1;

Concatenate (t) load * Resident test2;

// add unikey key

rename table t to tmp;

load rowno() as id, * Resident tmp;

DROP Table tmp, test1, test2;

Re: Adding a column to a concatenated table

mrybalko — Tue, 05 Aug 2014 14:41:35 GMT

RowNo() function works correct in concatenation. You do not need to make resident load.

Facts:
LOAD 
    RowNo() as id,
    A
INLINE [
    A
    1
    2
];

Concatenate(Facts)
LOAD 
    RowNo() as id,
    A,
    B
INLINE [
    A, B
    1, 11
    2, 22
];