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

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

Sun, 14 Feb 2010 02:23:30 GMT

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?

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

lironbaram — Sun, 14 Feb 2010 08:58:38 GMT

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;

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

Sun, 14 Feb 2010 12:46:08 GMT

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?

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

lironbaram — Sun, 14 Feb 2010 13:02:20 GMT

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

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

pdumas — Sun, 14 Feb 2010 14:51:32 GMT

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.

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

Mon, 15 Feb 2010 00:08:37 GMT

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

Mon, 15 Feb 2010 00:25:53 GMT

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

Mon, 15 Feb 2010 01:27:16 GMT

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?

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

johnw — Wed, 17 Feb 2010 02:07:06 GMT

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;

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

Anonymous — Wed, 17 Feb 2010 07:19:41 GMT

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.

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

Wed, 17 Feb 2010 13:48:17 GMT

I am adding a new column in the load resident, so I don't know why is concatenating.

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

Wed, 17 Feb 2010 14:14:32 GMT

Hi Igor,

Why dont you do this

Table:

LOAD A, B, C FROM T1

Concatenate (Table)

LOAD A, D, E FROM T2

Then in the Interface use the expression SUM(B) - SUM(D)

Alternatively if the calculations have to be done in the script do the concatenate in the script as a temporary table, then doing a resident load from that and use a group by function will manipulate the data accordingly.

Then add a preceding load taking one field away from the other field.

Hope this is of help,

Regards,

Neil