topic Re: Left Join table field to First table in QlikView

Left Join table field to First table

settu_periasamy — Tue, 24 Jun 2014 06:17:49 GMT

Hi,

i have 2 qvd file and i need to join them like the below code.

Table1:

LOAD A1,

B1,

if(not isnull(D1),0,A1) as C1 // D1 Field is in 2nd qvd

FROM

qvd\1.qvd

(qvd) where exists(F1);

Left Join

LOAD D1, E1 FROM qvd\2.qvd (qvd) where exists(E1);

store table1 to table1.qvd;

I'm getting an error field not found. I know D1 field not available in the 1st qvd file.

But i need the C1 field, if D1 is not null.

Could you please give the suggestion?

Re: Left Join table field to First table

jagan — Tue, 24 Jun 2014 06:24:09 GMT

Hi,

What are the key fields between Qvd1 and QVD2? Where is the column F1? can you attach some sample data and required output.

Regards,

Jagan.

Re: Left Join table field to First table

settu_periasamy — Tue, 24 Jun 2014 06:29:49 GMT

hi jagan,

QVD1 have A1, B1 and F1.

QVD2 have D1,E1 and F1.

I need the output like all the data from QVD1 and match the data from QVD2.

C1 is the calculated field from the QVD2.

i dont have the correct sample data.

Re: Left Join table field to First table

jagan — Tue, 24 Jun 2014 06:35:58 GMT

Hi,

On what basis QVD1 and QVD2 are joined? Is there any key fields?

Regards,

Jagan.

Re: Left Join table field to First table

settu_periasamy — Tue, 24 Jun 2014 06:39:14 GMT

Yes. F1 is the Key Field.

Re: Left Join table field to First table

vardhancse — Tue, 24 Jun 2014 06:41:07 GMT

to do any left join, we need to have one common field.

Re: Left Join table field to First table

MayilVahanan — Tue, 24 Jun 2014 06:43:13 GMT

Try like this

Load * from Qvd1;

Left join(A)

Load * from Qvd2;

Noconcatenate

Load A1, B1, if(not isnull(D1),0,A1) as C1, F1, D1, E1 Resident A;

Drop table A;

Re: Left Join table field to First table

Tue, 24 Jun 2014 06:43:51 GMT

Was F1 previously loaded? without that you can use it where exists. Or may be you haven't posted the entire script.

Re: Left Join table field to First table

er_mohit — Tue, 24 Jun 2014 06:46:27 GMT

table1:

load

A1, B1 ,F1 from QVD1 ;

left join(table1)

table2:

load

D1,E1 ,F1 from QVD2 where exist(F1);

noconcatenate

Final:

load * resident table1; //here you calculate the field for C1

drop table table1;

Re: Left Join table field to First table

jagan — Tue, 24 Jun 2014 07:35:13 GMT

Hi,

Try like this

Table1:

Load

A1, B1 ,F1

from QVD1 ;

Left Join(Table1)

Load

D1,E1 ,F1

from QVD2 where Exists(F1);

Final:

Load *,

If(Not IsNull(D1),0, A1) AS C1

Resident Table1;

Drop Table Table1;

Regards,

Jagan.

Re: Left Join table field to First table

jagan — Tue, 24 Jun 2014 07:49:11 GMT

Hi,

Instead of using Exists you can directly use Inner join instead of left join

Table1:

Load

A1, B1 ,F1

from QVD1 ;

Inner Join(Table1)

Load

D1,E1 ,F1

from QVD2;

Final:

Load *,

If(Not IsNull(D1),0, A1) AS C1

Resident Table1;

Drop Table Table1;

Regards,

Jagan.