Add rows to resident table from another resident table

[#1]

I have 2 Resident tables in my load script:

Table1 has fields A, B and C

Table2 has fields D, E and F

I need to append all the rows in Table2 to Table1 where D maps to A, E maps to B, and F maps to C; how can I do this?

[boorgura]

concatenate(Table1)

LOAD D as A,

E as B,

F as C

resident Table2;

drop table Table2;

[#3]

This is what I thought however it is resulting in a cartesian product.

[john_duffy]

Hello.

Can you provide an example using the two tables (a few records on each) and the desired results.

[#5]

Hi,

You have to give the alias to the first or second table so that the field name should be same.

Take bothe the tables like:

Test:

LOAD A,

B,

C

resident Table1;

LOAD D as A,

E as B,

F as C

resident Table2;

drop table Table2;

drop table Table1;

It will work automatically for concatenation.

Regards,

Ravi

[#6]

A

B

BudgetByQtr:

LOAD

ProjectID_SYS,

BudgetAmt,

[BudgetType3] AS BudgetType,

[QtrYear2] AS QtrYear

RESIDENT TempBudget3;

BudgetActualsTotal:

LOAD

TCalCostType as ABudgetType,

TQtrYear as AQtrYear,

TTotCostAmount as ABudgetAmt

RESIDENT BudgetActuals;

JOIN LOAD

ProjectID_SYS as ProjectID_SYS

RESIDENT [Project Details];

CONCATENATE (BudgetByQtr)

LOAD ProjectID_SYS,

ABudgetType as BudgetType,

ABudgetAmt as BudgetAmt,

AQtrYear as QtrYear

RESIDENT BudgetActualsTotal;

Figure A is a result of commenting out the CONCATENATE block and Figure B is a result of having the CONCATENATE block in there. As you can see in B, there are many record in there for the actuals, when there should only be 5 records that have "*Actual" in the BudgetType column.

[john_duffy]

Hello.

In order to determine if it is just the above code that is causing the issue, I would need to reload the application at my end. Can you attach a sample QlikView application with a small sample of TempBudget3, BudgetActuals and [Project Details] data create via an inline load. This way I can reload and test the logic.

A couple of things I notice about the code:

The join to [Project Details] to attach ProjectID_SYS is a full join. Every combination of the two tables will be created. Not knowing how the entire application works, is this full join okay? Should distinct be used?

Also, when [Project Details] is joined, the field ProjectID_SYS is not renamed to AProjectID_SYS. Therefore, if the BudgetActualsTotal table is not dropped, there will be a link to it and the final BudgetByQtr table.

Again, since I don't know what the final application looks like, these are just a couple of things I noticed about the code that might be causing an issue.

[boorgura]

Did you drop the second table after concatenating it to the first?

If not, it of course, will give cartesian product.

[#9]

I didn't add the key to Table2 when I built it that's what caused my cartesian product. Thanks for your assistance.