How to do a calculation in the script between 2 fields from 2 diff tables?

[#1]

Hello all.

I have 2 tables:

Table T1:
Columns: A, B and C

Table T2:
Columns A, D and E

The link between T1 and T2 is made by column A.

I need in the script to join both tables in one single logic table and also calculate the result of C * E.

Join the tables is easy, I am doing this:

myTable:
Load A, B, C From T1;
INNER JOIN Load A, D, E From T2;

However, how to calculate C * E?

If I try this:

myTable:
Load *, C * E;
Load A, B, C From T1;
INNER JOIN Load A, D, E From T2;

It says column E is unknown because the preceding load does not read T2. So, how can I do what I need to do?

[lironbaram]

hi

you can do somthing like that

myTableTemp:
Load A, B, C From T1;
INNER JOIN Load A, D, E From T2;

myTable:

load * ,

E*c as result

resident myTableTemp;

drop table myTableTemp;

[#3]

Humm, I've tried that but when I do, for some reason the result table has double records. The table in the DB has only about 400 records, the 1st table has that number of records but the second one adds those records twice. Any thought?

[lironbaram]

did you drop the originals tables in the end of the load script

[pdumas]

Hi,

I already asked approximatly the same question :

http://community.qlik.com/forums/p/25501/97365.aspx#97365

I have implemented John's answer.

It should also be suitable for your data

Pierre.

[#6]

Humm, I've tried that but when I do, for some reason the result table has double records. The table in the DB has only about 400 records, the 1st table has that number of records but the second one adds those records twice. Any thought?

[#7]

Humm, I've tried that but when I do, for some reason the result table has double records. The table in the DB has only about 400 records, the 1st table has that number of records but the second one adds those records twice. Any thought?

[#8]

Does anyone have any thought why when I do a Load Resident from an existing table, the result table has more then double records? How can I prevent that?

[johnw]

In my experience, when a join is creating rows that it shouldn't be creating, it's been because one of the tables has extra rows that I wasn't expecting. So I might have more than one row for some of the join key values in one of the tables. To look for the presence of "duplicate" rows, just do a table with A as the dimension, count(A) as the expression, and sort descending. If you see any values more than 1, you know you have duplicate rows for those values of A. Figuring out where they came from then involves adding additional fields to the table until you find a field that causes more than one row to appear for a single value of A. Sometimes, you can add ALL of your fields and it will never create more than one row. In that case, you really DO have duplicates. You can see them by doing a load from your original table to a new temp table, include new field recno() as ID, dropping the old table, and adding ID to your chart. That still doesn't necessarily tell you how they appeared, but it may be useful information.

Assuming you solve your too many rows problem, I try to avoid loading one table from another and dropping the first, because I suspect it uses, well, double the memory for that table. I don't have a ton of memory to toss around. So I'd add on the new field with a left join:

myTable:
Load A, B, C From T1;
INNER JOIN Load A, D, E From T2;
LEFT JOIN Load A, C*E as Something
RESIDENT myTable;

[Anonymous]

I would suspect that when you do a resident load resulting in double the rows, you get an automatic concatenation at some point. Maybe you're doing a resident load without changing any fieldnames from the original table?

Try pasting the current load script here and we'll help you look at what the problem might be.