Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join problem

Hi

I want to join two tables, and insert the new calculate data on the first one, look at the example:


Table1:

MeasureValueDate
Units1.001
Dollar25.001
Units1.002
Dollar25.002

Table2:

DateRate
11.05
21.09

I would like to include on the Table 1, this lines that means (Dollar: Value * Rate on the same Date)

MeasureValueDate
LocalValue26,251
LocalValue27,252

I'm try to do like this, but I continue have a problem:

JOIN (Table2)

LOAD

  'LocalValue' as [Measure],

  [Value]*[Rate] AS [Value]

RESIDENT Table1

WHERE [Measure] = 'Dollar'

but I'm geting this error message:

Error Field not found - <Rate>

What I'm doing wrong?

--- EDITED

Example here:

Table1:

Load * inline

[

Measure,Value,Date

Units,1,1

Dollar,25,1

Units,1,2

Dollar,25,2

];

Table2:

Load * inline

[

Date,Rate

1,2

2,3

];

Table1:

JOIN (Table2)

LOAD

  'LocalValue' as [Measure],

  [Value]*[Rate] AS [Value]

RESIDENT Table1

WHERE [Measure] = 'Dollar'

4 Replies
prieper
Master II
Master II

Not quite clear on the difference in line 1 and 2 resp. 3 and 4 in the first table. Please explain.

If there is a 1:1 ratio, would recomment to use a mapping:

mapRate: MAPPING LOAD Date, Rate FROM ....

Data:

LOAD

     *,

    Value * APPLYMAP('mapRate', Date)          AS Local

FROM

     ....;

If there are multiple currencies involved, you may consider a combined key.

Else check on the INTERVALMATCH-function

HTH Peter

sunny_talwar

May this:

Table1:

Load * inline

[

Measure,Value,Date

Units,1,1

Dollar,25,1

Units,1,2

Dollar,25,2

];

Table2:

Load * inline

[

Date,Rate

1,2

2,3

];

Join (Table1)

LOAD

  'Dollar' as [Measure],

  Date,

   Rate

RESIDENT Table2;

Table:

LOAD *,

  Value * Rate as [Total Value]

Resident Table1;

DROP Tables Table1, Table2;

Output:

Attaching the sample application for ease of understanding.

HTH

Best,

S

maxgro
MVP
MVP

1.png

Table1:

Load * inline [

Measure,Value,Date

Units,1,1

Dollar,25,1

Units,1,2

Dollar,25,2

];

Table2:

Load * inline [

Date,Rate

1,1.05

2,1.09

];

left join (Table2) load

'LocalValue' as Measure,

Value,

Date

Resident Table1

Where Measure = 'Dollar';

Concatenate (Table1)

load Measure, Value*Rate as Value, Date

Resident Table2;

DROP Table Table2;

Anonymous
Not applicable
Author

Hugs