Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Question on how to put the JOIN Statment

I have a question regarding Qlik Sense on how to put the JOIN Statment.

I would like to select on the File B only records that contains the File A 

As you will see I have the same field on both Files (Field: Order).

File A:

  LOAD "wo_nbr" as "Order",

     "wo_ord_date" as "Date";

  QL SELECT "wo_nbr",

     "wo_ord_date",

  ROM MFGPROD.PUB."wo_mstr" where "wo_nbr" like 'PS%' and "wo_status" = 'R';

File B:

LOAD "wod_nbr" as "Order",

"wod_qty_req" as "Quantity";

  SQL SELECT "wod_nbr",

  "wod_lot",

  "wod_iss_date",

  "wod_part",

  ROM MFGPROD.PUB."wod_det" WHERE "wod_nbr" like 'PS%';

I will appreciate your help.

Regards

 

6 Replies
pradosh_thakur
Honored Contributor II

Re: Question on how to put the JOIN Statment

Use left join If you want all the data from A and only matched data from B.

If you want matched data from both use inner join

use Keyword LEFT JOIN / inner join in between the both table

regards

Pradosh

Learning never stops.
MVP
MVP

Re: Question on how to put the JOIN Statment


File A:

  LOAD "wo_nbr" as "Order",

     "wo_ord_date" as "Date";

  QL SELECT "wo_nbr",

     "wo_ord_date",

  ROM MFGPROD.PUB."wo_mstr" where "wo_nbr" like 'PS%' and "wo_status" = 'R';


[File B]:

LOAD "wod_nbr" as "Order",

"wod_qty_req" as "Quantity"

where exists(Order, wod_nbr)

;

  SQL SELECT "wod_nbr",

  "wod_lot",

  "wod_iss_date",

  "wod_part",

  ROM MFGPROD.PUB."wod_det" WHERE "wod_nbr" like 'PS%';

mov
Esteemed Contributor III

Re: Question on how to put the JOIN Statment

What type of JOIN?

Assuming it is left outer join, I'd use:

LOAD

wo_nbr as Order,

wo_ord_date as Date,

wod_qty_req as Quantity,

wod_lot as Lot,

wod_iss_date as "ISS Date",

wod_part as Part

;

SQL SELECT

m.wo_nbr,

m.wo_ord_date,

d.wod_qty_req,

d.wod_lot,

d.wod_iss_date,

d.wod_part

FROM MFGPROD.PUB.wo_mstr m

LEFT OUTER JOIN MFGPROD.PUB.wod_det d on d.wod_nbr=m.wo_nbr

WHERE m.wo_nbr like 'PS%'

pradosh_thakur
Honored Contributor II

Re: Question on how to put the JOIN Statment

hi filipe

if you got your answer please close the thread by marking answer helpful and correct

Learning never stops.
Not applicable

Re: Question on how to put the JOIN Statment

I'm still have problems with it.

FILE A

SQL SELECT FIELD A,

FIELD B1,

FIELD C2,

FIELD D3,

FIELD E4;

FILE B

SQL SELECT FIELD A,

FIELD B2,   

FIELD C2,

FIELD D2,  

FIELD E2;

I would like to read all the records from File A and read only records from File B that joins for field A with File A

 

Any help will be appreciated.

 

MVP
MVP

Re: Question on how to put the JOIN Statment

FILEA:

SQL SELECT FIELDA,

FIELDB1,

FIELDC2,

FIELDD3,

FIELDE4;

FILEB:

LOAD

*

WHERE

exists(FIELDA)

;

SQL SELECT FIELDA,

FIELDB2,  

FIELDC2,

FIELDD2, 

FIELDE2;

Community Browser