CODEWITHSUNDEEP
joinqlikview
Thyago Quintas
Thyago Quintas

Reputation: 669

QlikView Join; What I'm doing wrong?

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 -

What I'm doing wrong?

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'

Upvotes: 1

Views: 617

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

At the point when you try "join (Table2) " fields Rate and Value do not exists in the same table from which you are trying to load (Table1).

You need to have fields Value and Rate in one table before join the LocalValue calculation. Your script need to look like this:

Table1:
Load * inline
[
Measure,Value,Date
Units,1,1
Dollar,25,1
Units,1,2
Dollar,25,2
]; 

join

Table2:
Load * inline [ 
Date,Rate
1,2
2,3
];

JOIN (Table1)

//Table1:
LOAD
  'LocalValue' as [Measure],
  [Value]*[Rate] AS [Value]
RESIDENT Table1
WHERE [Measure] = 'Dollar'

And the result table will be:

Measure     Value   Date    Rate
Dollar      25      1       2
Units       1       1       2
Dollar      25      2       3
Units       1       2       3
LocalValue  50      -       -
LocalValue  75      -       -

Stefan

Upvotes: 1

Related Questions