Why Can't I get Len Trim to Work

oliveton — Thu, 26 Jul 2018 14:45:51 GMT

I've been trying to remove all numbers where PopCnd (or XPOPCODE) = 'N' or 'O' or 'P' or 'T' but only for the ones which have nothing under PartStatusTwo (or [PART STATUS II]).

I used Where len(Trim([PART STATUS II]))> 0; combined with Where not Match(XPOPCODE, 'N' , 'P' , 'T'); but this removes all numbers with PopCnd = to 'N' 'O' 'P' or 'T' not just the ones which have no description.

"PartStatusTwo" is a separate file, PopCnd comes from "CndAll."

I then tried joining PartStatusTwo and CndAll and did FROM........

Where not Match(XPOPCODE, 'N' , 'P' , 'T' and [PART STATUS II] = 0); which does not work.

Hopefully it makes sense what I'm trying to do, been trying to figure this out for a while, would appreciate the help!!

Below are the 2 things I tried in context.

Thanks all

PartStatusTwo:

LOAD Distinct

Text([MAI

COMPRESSED

PARTNUMBER]) as %Cram,

[PART STATUS II] as PartStatusTwo

FROM

(ooxml, embedded labels, table is Sheet1);

Right 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$) Where not Match(XPOPCODE, 'N' , 'P' , 'T' and [PART STATUS II] = 0);

AND

PartStatusTwo:

LOAD Distinct

Text([MAI

COMPRESSED

PARTNUMBER]) as %Cram,

[PART STATUS II] as PartStatusTwo

FROM

(ooxml, embedded labels, table is Sheet1) Where len(Trim([PART STATUS II]))> 0;

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$) Where not Match(XPOPCODE, 'N' , 'P' , 'T');

Re: Why Can't I get Len Trim to Work

YoussefBelloum — Thu, 26 Jul 2018 15:29:43 GMT

Hi,

I can't find XPOPCODE field, you rename it as PopCnd but can't find it..

Re: Why Can't I get Len Trim to Work

oliveton — Thu, 26 Jul 2018 16:41:00 GMT

Hi,

It's highlighted in blue in my first example. About 12 lines under the link.

Thanks

topic Re: Why Can't I get Len Trim to Work in QlikView

Why Can't I get Len Trim to Work

Re: Why Can't I get Len Trim to Work

Re: Why Can't I get Len Trim to Work