How to Apply a Mapping Load When the Calculation Must Occur Between Fields Not in Same Table?

[oliveton]

Is someone able to explain (and provide examples) how to use a mapping load to take my two fields 'PopCnd' (from CndAll) and 'PartStatusTwo' (from PartStatusTwo) which are from different tables to calculate something like Where Not Match(PopCnd, 'N' , 'P' , 'T' and PartStatusTwo = '0')

Trying to remove everything where PopCnd is = to 'N' or 'P' or 'T' but ONLY if there is nothing in 'PartStatusTwo.'

Would really appreciate some help!

[olivierrobin]

hello

why don't you join PartStatusTwo with CndAll on key %Cram

and then

load

from CndAll

where not(match(popCnd,'N',P','T') and isnull(PartStatusTwo))

You may have to adapt the test to you real requirements

[oliveton]

How exactly would this look? Because I seem to keep messing that up........even if it runs without any errors it still hasn't done what I want it to do.

[olivierrobin]

left join(CndAll)

load

%Cram,

CndAll

resident PartStatusTwo

and then the load with where clause

[oliveton]

I did this and it says my field can't be found.

Left Join (CndAll)

LOAD

%Cram,  

CndAll

Resident PartStatusTwo   

Where not(Match(PopCnd, 'N' , 'P' , 'T') and IsNull(PartStatusTwo));

[olivierrobin]

you have to do it in 2 parts

1 - join and in the join you should precise the keys of then and the columns you want to add to your table

2 - load with where clause

there-s a mistake in the join, you should use parstatustwo instead of cndall in the list of columns (you can use only column names from the table you load)

[oliveton]

OK yeah, was wondering about the left join (CndAll)

I will try this, thank you!!!!

[neelamsaroha157]

try this -

PartStatusTwo:

Mapping

LOAD Distinct

Text([MAI COMPRESSED PARTNUMBER]) as %Cram,

[PART STATUS II]  as PartStatusTwo

FROM

(ooxml, embedded labels, table is Sheet1);

CndAll:

LOAD *, if(Match(PopCnd , 'N', 'P' , 'T' ) and (IsNull(PartStatusTwo) or Len(PartStatusTwo) <1),0,1) as Flag

;

LOAD Distinct

    Text(XCLEVITEM) as %Cram,

    ApplyMap('PartStatusTwo',Text(XCLEVITEM), '*Unspecified') as    PartStatusTwo,

    text(XPUBLITEM) as CNDPublished,

     XCUSTITEM,

     XSUPERSED as SupersededCND,

     XSTATUS as Status,

     XJOBBER$ as JN,

     XINVOICE$ as WD,

     XTIER_DISC,

     XCORECHG$ as CoreCharge,

     XITEMCLASS as ItemClass,

     XPRICECLAS,

     XPRCLSDESC,

     XPOPCODE as PopCnd ,

     XDOMINA as PopStatus,

        XBRAND,

     XVEHTYP as VehicleType,

   Text( XUPCCODE) as UPC,

   Text(XEANNUMBER) as EAN,

     XCOORIGIN as CountryofOrigin,

     XHARMONIZE as HarmonizedCode,

     XUNITISSUE as UnitOfIssue,

     XSTDPKQTY as StdPkgQty,

     XMAXCARQTY as MaxCar,

     XAPPLDESC as ApplicationCND,

     XITEMDESC as Desc,

     XWEIGHT as Weight,

     XLENGTH as Length,

     XWIDTH as Width,

     XHEIGHT as Height,

     XEHC

FROM

(biff, embedded labels, table is CND_888100_Filters_Stats_Gasket$);

NoConcatenate

LOAD *

Resident CndAll

where Flag = '1'

;

DROP Table CndAll;

DROP Field Flag;

[oliveton]

So I tried this; and it still says it can't find the resident portion and where clause......which I changed a little because it seems to be removing these things but also removing numbers which have a null PartStatusTwo but with different PopCnd that I actually want to keep...........

Left Join (PartStatusTwo)

CndAll:

LOAD Distinct

    Text(XCLEVITEM) as %Cram,  

    text(XPUBLITEM) as CNDPublished,

     XCUSTITEM,

     XSUPERSED as SupersededCND,

     XSTATUS as Status,

     XJOBBER$ as JN,

     XINVOICE$ as WD,

     XTIER_DISC,

     XCORECHG$ as CoreCharge,

     XITEMCLASS as ItemClass,

     XPRICECLAS,

     XPRCLSDESC,

     XPOPCODE as PopCnd ,

     XDOMINA as PopStatus,

        XBRAND,

     XVEHTYP as VehicleType,

   Text( XUPCCODE) as UPC,

   Text(XEANNUMBER) as EAN,

     XCOORIGIN as CountryofOrigin,

     XHARMONIZE as HarmonizedCode,

     XUNITISSUE as UnitOfIssue,

     XSTDPKQTY as StdPkgQty,

     XMAXCARQTY as MaxCar,

     XAPPLDESC as ApplicationCND,

     XITEMDESC as Desc,

     XWEIGHT as Weight,

     XLENGTH as Length,

     XWIDTH as Width,

     XHEIGHT as Height,

     XEHC

FROM

(biff, embedded labels, table is CND_888100_Filters_Stats_Gasket$);

CndAll:

LOAD Distinct

    Text(XCLEVITEM) as %Cram,  

     XPOPCODE as PopCnd

Resident PartStatusTwo

Where(PopCnd = 'N' or 'P' or 'T') and PartStatusTwo = '0';

[olivierrobin]

syntactically, there's a problem with

Left Join (PartStatusTwo)

CndAll:

you can't combine the 2.

in case of a join ,the syntax is

join(table)

load .......