topic Re: Join qlikview tables with cast in QlikView

Join qlikview tables with cast

Wed, 06 Apr 2016 12:00:13 GMT

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!

Re: Join qlikview tables with cast

robert_mika — Wed, 06 Apr 2016 12:39:59 GMT

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)

Re: Join qlikview tables with cast

Wed, 06 Apr 2016 13:15:58 GMT

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.

Re: Join qlikview tables with cast

robert_mika — Wed, 06 Apr 2016 13:22:52 GMT

So does my anwer satisfied you?

If yes you can mark the question as answered.

Re: Join qlikview tables with cast

Wed, 06 Apr 2016 13:32:39 GMT

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

Re: Join qlikview tables with cast

robert_mika — Wed, 06 Apr 2016 13:36:01 GMT

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.

Re: Join qlikview tables with cast

Wed, 06 Apr 2016 13:56:54 GMT

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

Re: Join qlikview tables with cast

robert_mika — Wed, 06 Apr 2016 14:07:58 GMT

So which fields are common in both tables?

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

Re: Join qlikview tables with cast

Wed, 06 Apr 2016 14:16:00 GMT

tb1.currency = tb2.currency

tb1.time = tb2.date