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

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
Master II
Master II

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.
maxgro
MVP
MVP


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%';

Anonymous
Not applicable
Author

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
Master II
Master II

hi filipe

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

Learning never stops.
Not applicable
Author

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.

 

maxgro
MVP
MVP

FILEA:

SQL SELECT FIELDA,

FIELDB1,

FIELDC2,

FIELDD3,

FIELDE4;

FILEB:

LOAD

*

WHERE

exists(FIELDA)

;

SQL SELECT FIELDA,

FIELDB2,  

FIELDC2,

FIELDD2, 

FIELDE2;