topic Re: Join problem in QlikView

Join problem

Wed, 18 Mar 2015 16:41:54 GMT

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

Table1:

Measure	Value	Date
Units	1.00	1
Dollar	25.00	1
Units	1.00	2
Dollar	25.00	2

Table2:

Date	Rate
1	1.05
2	1.09

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

Measure	Value	Date
LocalValue	26,25	1
LocalValue	27,25	2

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'

Re: Join problem

prieper — Wed, 18 Mar 2015 20:26:04 GMT

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

sunny_talwar — Wed, 18 Mar 2015 20:39:56 GMT

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,

Re: Join problem

maxgro — Wed, 18 Mar 2015 20:53:13 GMT

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;

Re: Join problem

Anonymous — Wed, 18 Mar 2015 22:13:08 GMT

Hugs