Concatenate resident table

[micsim123]

Hi!

In my load script I want to concatenate a previously loaded table to my one fact table. The scripts principle goes like this:

FACT:

Load *

From table1 ;

Concatenate FACT:

Load *

From table2;

Concatenate FACT:

Load *

From table3;

// Now I need to first load table4 without concatenation, and make some calculations i table4

NoConcatenate

Table4:

Load *

From table4;

At the end I now want to include Table4 in my FACT.

I have tried:

Concatenate FACT:

Load *

Resident Table4 ;

Drop Table Table4;

But it does not work. I lose all variables from table4

Any ideas?

Regards Michael

[Anonymous]

Hi Michael,

try this:

concatenate (FACT)

load *

resident FACT4;

drop table FACT4;

[micsim123]

Hi Rodolfo

There is no FACT4 table, so it won't work with:

resident FACT4;

Regards

Michael

[Thiago_Justen_ · Accepted solution]

Michael you are missing something...

The correct syntax here is:

Concatenate (TABLE_NAME)

Load

*

From OTHER_TABLE;

[Thiago_Justen_]

Another important thing: concatenate only works with tables that have same fields names into them.

For instance:

Table_1:

Load

FIELD_1,

FIELD_ID

From DB;

Concatenate (Table_1)

Load

FIELD_2 AS FIELD_1,

FIELD_ID2 AS FIELD_ID

From DB1;

If it is not your case I'd suggest you Left Join tables.

[Anonymous]

sorry Michael, I intended to say :

concatenate (FACT)

load *

Table4;

drop Table 4;

[micsim123]

Hi Thiago

You're right.

I should really pay more attention to the syntax details.

It works with:

Concatenate (FACT)

Load *

Resident Table4 ;

Instead of:

Concatenate FACT:

Load *

Resident Table4 ;

thanks.

[Thiago_Justen_]

Don't mention it. Happy to help you.

[Jjohnston]

I have a similar situation, but no matter what I try I can't get the Composite field to create.  I'm sure I'm just missing something in the syntax, but I don't have enough experience to identify what the miss is.  HELP!

//new table name

[CUA Pkey Position]:

//load table to append

NoConcatenate Load* RESIDENT [Position Master];

Left Join 

//load table received data

LOAD* RESIDENT [CUA with Pkey];

//define which fields will be in the new table all imported fields from both plus a composite field?

LOAD*, 

 //All fields from both Residents PLUS Need a composite field that is:"Employee ID"(Position Master) & '|' & "Position Number"(Position Master) & '|' & "Processed Date"(CUA with Pkey) as "Employee-Date Key"

[CUA Pkey Position]

 //define any other criteria that isn't a common join

WHERE ("Processed Date" >= "Effective Start" and "Processed Date" <= "Effective End");