Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
Where Exists(%MasterKey, MATERIAL & LOCATION);
Assuming you are looking to restrict based on both MATERIAL & LOCATION
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
Where Exists(%MasterKey, MATERIAL & LOCATION);
Assuming you are looking to restrict based on both MATERIAL & LOCATION
thank you it worked out