loading multiple tables and how to load only matching values of table 2 into table 1

[bushpalaamarnat]

This is my script, now i want to load only those materials from product master which are in the DFC table. Could you help.

DFC:

CrossTable(Weeks, DFC, 😎

LOAD [Material Code],

     [Material Description],

     [Customer Location],

     [Location Description],

     [Max SFT DFC],

     [Safety Days Supply],

     [Min SFT DFC],

     [Material Code] &   [Customer Location] as %MasterKey,

  //   if( [Customer Location]=FR50,[Customer Location]) as Franchise,

     [W48-17],

     [W49-17],

     [W50-17],

     [W51-17],

     [W52-17],

     [W01-18],

     [W02-18],

     [W03-18],

     [W04-18],

     [W05-18],

     [W06-18],

     [W07-18],

     [W08-18],

     [W09-18],

     [W10-18],

     [W11-18],

     [W12-18],

     [W13-18],

     [W14-18],

     [W15-18],

     [W16-18],

     [W17-18],

     [W18-18],

     [W19-18],

     [W20-18],

     [W21-18],

     [W22-18],

     [W23-18],

     [W24-18],

     [W25-18],

     [W26-18],

     [W27-18],

     [W28-18],

     [W29-18],

     [W30-18],

     [W31-18],

     [W32-18],

     [W33-18],

     [W34-18],

     [W35-18],

     [W36-18],

     [W37-18],

     [W38-18],

     [W39-18],

     [W40-18],

     [W41-18],

     [W42-18],

     [W43-18],

     [W44-18],

     [W45-18],

     [W46-18],

     [W47-18],

     [W48-18]

FROM

[..\Data\EXcel\DFC.xlsx]

(ooxml, embedded labels, table is Sheet1);

Volume:

CrossTable(Weeks2, Volume, 5)

LOAD [Material Code] ,

     [Material Description],

     [Customer Location] ,

     [Location Description],

[Material Code] &   [Customer Location] as %MasterKey,

     [W48-17],

     [W49-17],

     [W50-17],

     [W51-17],

     [W52-17],

     [W01-18],

     [W02-18],

     [W03-18],

     [W04-18],

     [W05-18],

     [W06-18],

     [W07-18],

     [W08-18],

     [W09-18],

     [W10-18],

     [W11-18],

     [W12-18],

     [W13-18],

     [W14-18],

     [W15-18],

     [W16-18],

     [W17-18],

     [W18-18],

     [W19-18],

     [W20-18],

     [W21-18],

     [W22-18],

     [W23-18],

     [W24-18],

     [W25-18],

     [W26-18],

     [W27-18],

     [W28-18],

     [W29-18],

     [W30-18],

     [W31-18],

     [W32-18],

     [W33-18],

     [W34-18],

     [W35-18],

     [W36-18],

     [W37-18],

     [W38-18],

     [W39-18],

     [W40-18],

     [W41-18],

     [W42-18],

     [W43-18],

     [W44-18],

     [W45-18],

     [W46-18],

     [W47-18],

     [W48-18]

FROM

[..\Data\EXcel\Volume.xlsx]

(ooxml, embedded labels, table is Sheet1);

Drop Fields 

     [Location Description],[Material Code] ,

     [Material Description],

     [Customer Location] ,

     [Location Description],Weeks2 from Volume;

Left join(DFC)

ProductMaster:

LOAD

LOCATION,

     [LOCATION TYPE],

MATERIAL,

     [MATERIAL DESCRIPTION],

     [SNP PLANNER],

     [LIFECYCLE STATUS],

     [PROCUREMENT TYPE],

     [MINIMUM LOT SIZE],

     [ROUNDING VALUE],

   MATERIAL & LOCATION as %MasterKey

FROM

(qvd);

[sunny_talwar · Accepted solution]

You can either do Left Keep (instead of Left Join), or you can use Where Exists like this

ProductMaster:

LOAD LOCATION,

     [LOCATION TYPE],

     MATERIAL,

     [MATERIAL DESCRIPTION],

     [SNP PLANNER],

     [LIFECYCLE STATUS],

     [PROCUREMENT TYPE],

     [MINIMUM LOT SIZE],

     [ROUNDING VALUE],

     MATERIAL & LOCATION as %MasterKey

FROM (qvd)

Where Exists(%MasterKey, MATERIAL & LOCATION);

Assuming you are looking to restrict based on both MATERIAL & LOCATION

[bushpalaamarnat]

thank you it worked out