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

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

oliveton — Fri, 27 Jul 2018 12:30:14 GMT

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!

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

olivierrobin — Fri, 27 Jul 2018 12:46:07 GMT

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

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

oliveton — Fri, 27 Jul 2018 12:57:26 GMT

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.

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

olivierrobin — Fri, 27 Jul 2018 13:05:39 GMT

left join(CndAll)

load

%Cram,

CndAll

resident PartStatusTwo

and then the load with where clause

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

oliveton — Fri, 27 Jul 2018 13:42:14 GMT

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));

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

olivierrobin — Fri, 27 Jul 2018 13:47:59 GMT

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)

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

oliveton — Fri, 27 Jul 2018 13:50:02 GMT

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

I will try this, thank you!!!!

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

neelamsaroha157 — Fri, 27 Jul 2018 13:51:20 GMT

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;

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

oliveton — Fri, 27 Jul 2018 15:22:51 GMT

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

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

olivierrobin — Fri, 27 Jul 2018 15:26:09 GMT

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