Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Folks
How would I perform this join in QV:
select *
from ExecutionReport exec
left join FXRate fx
on fx.currency = exec.currency
and fx.date = cast(exec.[time] as date)
Thanks in advance!
If you got only one common field Qlikview will join both tables on that field
If you got more that one column with the same name you need to create synthetic key
in both table.
When you connect Qlikview to SQL base you will have to join both tables within the script similar to this
:
fx:
Load
Your Fields,
currency&'-' &Date as %Key;
SQL SELECT *
FROM ....
left join(T1)
T2:
Load
Your Fields
Currency &'-' & cast(time ) as %Key
SQL SELECT *
FROM ....
Change the cast to Date,Date#,or Timestamp - depends on what the Date from the first table is..
Are you converting time to Date?
Post your script if you are still struggling.
Feeling Qlikngry?
Yea exactly time is a datetime field and i need to cast it to a date.
So I was wondering what the "Qlikview way" of doing the above SQL would be for those 2 joins.
So does my anwer satisfied you?
If yes you can mark the question as answered.
I think im struggling even at an earlier stage. I have created an extract stage where I import FXRate table. See below. Now I need to perform the join by joining on date = time and currency = currency.
Execution_
LOAD *
FROM Execution
And then........
Left join(Execution)
LOAD date,
currency,
rate_eur
FROM FXRate
you can not do Load * in one table and then fields name from other.
How does your SQL querry in Qlikview script looks like?
BTW you need a key to join both table - see my first post.
Execution:
LOAD
handling_instruction,
identifier,
last_mkt,
market_order_id,
market_trade_id,
msg_seq_no,
open_volume,
order_id,
own_ref,
parent_order_id,
price,
session_counter,
session_time,
side,
sub_exec_broker,
time_in_force,
trade_price,
traded_amount,
transaction_client_id,
transaction_user_id,
user_id,
volume,
%key_MarketDateCurr,
TurnoverSEK
FROM
(qvd);
Left join(Execution)
LOAD date,
currency,
rate_eur
FROM
(qvd);
This is what it looks like
So which fields are common in both tables?
I can not see any Date or time field in first table
tb1.currency = tb2.currency
tb1.time = tb2.date