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

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'

Tags (1)
4 Replies
prieper
Honored Contributor II

Re: Join problem

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

Re: Join problem

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

MVP
MVP

Re: Join problem

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;

emendieta
New Contributor III

Re: Join problem

Hugs

Community Browser