topic Re: Left join help in QlikView

Left join help

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below issue.

I have Two tables like below. Here I am doing left join with DATE field.

While doing left join I am getting -129 error its like timeout error. After refreshing the browser also getting same error.

When I do the outer join its loading, but I need to combine these two tables into one.

Please suggest me to combine these two tables into one instead of left join with apply map or any other way.

Table1:

Date(Date#(Date(Timestamp(Date),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

ID,

CURRENCY,

RATE,

VALUE

From ....

left join(Table1)

Table2:

Date(Date#(Date(Timestamp(STARTDATE),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

FROM_CCY,

TO_CCY

From ....

Thanks in advance

Re: Left join help

shiveshsingh — Fri, 13 Apr 2018 05:28:11 GMT

I can see two common fields in your both the tables, DATE and From.

are you joining on both these points? What is the requirement from the second table.

Re: Left join help

mahitham — Fri, 13 Apr 2018 05:36:33 GMT

Hi Shivesh,

The common field between two tables is only DATE field.

From is command with table name not field.

I am trying to do left join these two tables by DATE field. Here Table1 is the Fact table.

Table2 is the dimension table. From second table DATE related FROM_CCY Need to get after joining with table1.

Can avoid TO_CCY field.

Thanks in advance.

Re: Left join help

shiveshsingh — Fri, 13 Apr 2018 05:41:29 GMT

Try this

Table1: load

Date(Date#(Date(Timestamp(Date),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

ID,

CURRENCY,

RATE,

VALUE

From .... ;

Table2: mapping load

Date(Date#(Date(Timestamp(STARTDATE),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

FROM_CCY,

From .... ;

Final:

load ApplyMap('Table2',DATE) as FROM_CCY,ID,

CURRENCY,

RATE,

VALUE

Resident Table1;

drop table Table1;

Re: Left join help

mahitham — Fri, 13 Apr 2018 05:45:19 GMT

Hi Shivesh,

Thanks for your reply.

When we do apply map do we get all the date fields from the fact table or only common dates between two tables. Please confirm.

Re: Left join help

PrashantSangle — Fri, 13 Apr 2018 05:48:58 GMT

can you share sample data??

Regards,

Re: Left join help

shiveshsingh — Fri, 13 Apr 2018 06:00:21 GMT

You'll get all the data from table1 and corresponding FROM_CCY from second table.

incase From_CCY is not present for some data in table 2, you can define NA like ApplyMap('Table2',DATE,'NA')

Re: Left join help

YoussefBelloum — Fri, 13 Apr 2018 08:04:17 GMT

Hi,

one question, why do you TimeStamp your date field and Date it again ?

...Date(Timestamp(Date),'DD/MM/YYYY')... ?

Re: Left join help

mahitham — Fri, 13 Apr 2018 13:51:22 GMT

Hi Youssef,

My date field is in DD/MM/YYYY hh:mm:ss format

to convert it to DD/M//YYYY I have used the below expression

Date(Date#(Date(Timestamp(STARTDATE),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

Please let me know am I did anything wrong here

Thanks in advance.

Re: Left join help

Anil_Babu_Samineni — Fri, 13 Apr 2018 14:03:49 GMT

Simple do this?

Date(Date#(STARTDATE, 'DD/MM/YYYY hh:mm:ss'), 'DD/M/YYYY') as DATE

Date(STARTDATE, 'DD/M/YYYY') as DATE

Re: Left join help

passionate — Fri, 13 Apr 2018 14:25:43 GMT

try joining by numeric value of date i.e num(Date)

Re: Left join help

vishalarote — Sat, 14 Apr 2018 06:48:12 GMT

Convert date first timestamp to actual

for e.g.

Date(Date#(timestamp#(Date,'DD/MM/YYYY hh:mm;ss'),'DD/MM/YYYY'),'DD/MM/YYYY') as Date

Date(Date#(timestamp#(STARTDATE,'DD/MM/YYYY hh:mm;ss'),'DD/MM/YYYY'),'DD/MM/YYYY') as Date

and then left join that two tables.