Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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?

Tags (1)
11 Replies
lironbaram
Honored Contributor II

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

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;

Not applicable

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

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
Honored Contributor II

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

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

pdumas
New Contributor III

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

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.

Not applicable

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

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?

Not applicable

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

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?

Not applicable

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

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?

MVP
MVP

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

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;

jsn
Honored Contributor

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

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.

Community Browser