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 qlikview tables with cast

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!

8 Replies
robert_mika
Master III
Master III

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?

How To /Missing Manual(25 articles)

Not applicable
Author

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.

robert_mika
Master III
Master III

So does my anwer satisfied you?

If yes you can mark the question as answered.

Not applicable
Author

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

robert_mika
Master III
Master III

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.

Not applicable
Author

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

robert_mika
Master III
Master III

So which fields are common in both tables?

I can not see any Date or time field in first table

Not applicable
Author

tb1.currency = tb2.currency

tb1.time = tb2.date