topic Add rows to resident table from another resident table in QlikView

Add rows to resident table from another resident table

Wed, 17 Nov 2010 20:04:49 GMT

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?

Add rows to resident table from another resident table

boorgura — Wed, 17 Nov 2010 20:06:54 GMT

concatenate(Table1)

LOAD D as A,

E as B,

F as C

resident Table2;

drop table Table2;

Add rows to resident table from another resident table

Thu, 18 Nov 2010 13:37:44 GMT

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

Add rows to resident table from another resident table

john_duffy — Thu, 18 Nov 2010 14:00:07 GMT

Hello.

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

Add rows to resident table from another resident table

Thu, 18 Nov 2010 14:09:30 GMT

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,

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

Add rows to resident table from another resident table

Thu, 18 Nov 2010 14:37:00 GMT

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.

Add rows to resident table from another resident table

john_duffy — Fri, 19 Nov 2010 14:17:26 GMT

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.

Add rows to resident table from another resident table

boorgura — Fri, 19 Nov 2010 19:59:31 GMT

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

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

Add rows to resident table from another resident table

Fri, 19 Nov 2010 20:10:24 GMT

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