Left keep function

[#1]

Hi all

I have a problem with a left keep function.

First I'll find the articles that I have on stock by

StockCompare:

LOAD

"MATERIAL_ID" as ArticleID;

//    AMOUNTFREEWMS,
//    AMOUNTBLOCKEDWMS,
//    AMOUNTQIWMS
SQL SELECT *
FROM ANT.WMSSTOCKCOMPARE
where AMOUNTFREEWMS + AMOUNTBLOCKEDWMS + AMOUNTQIWMS > 0;

Then I'll get som additional data which should only be on the articles that I have on stock, so therefore I use

DCU:
Left keep(StockCompare)
LOAD
     'C190' as Site,

//    NAME,    

subfield(NAME, '/', 1) As Article,

    DEPTH/10 as Length,
    WIDTH/10 as Width,
    HEIGHT/10 as Height,
    CAPACITY as SKU,
    if(RangeMax(DEPTH,WIDTH,HEIGHT)=DEPTH,0,1) as Diff.L,
    if(DEPTH=RangeMax(DEPTH,WIDTH,HEIGHT)and(HEIGHT=RangeMin(DEPTH,WIDTH,HEIGHT)),0,1) as Diff.W,
    if(RangeMin(DEPTH,WIDTH,HEIGHT)=HEIGHT,0,1) as Diff.H,
    if(RangeMax(DEPTH,WIDTH,HEIGHT)=DEPTH,0,1)+if(DEPTH=RangeMax(DEPTH,WIDTH,HEIGHT)and(HEIGHT=RangeMinDEPTH,WIDTH,HEIGHT)),0,1 +if(RangeMin(DEPTH,WIDTH,HEIGHT)=HEIGHT,0,1) as Sum_Diff,

Round (COLLIVOLUME/1000000000,0.001) as Volume,

COLLIWEIGHT/1000 as Weight,

"MATERIAL_ID" as ArticleID;

SQL SELECT *
FROM ANT.WMSPACKAGINGUNITTYPE
where HOSTACTIVE = 1 and NUMBEROFCOLLIS > 0;

My problem is now that I have some BOM articles (components) that have 9 digits but in my stockcompare these is stored on a 7 digigt number and therefore i do not get these displayed when I use the "left keep" function.

EX. Article 1234567 is an BOM article that is made by the two components 123456701 and 123456702. The data (length, width, height, volume and weight) is on 123456701 and 123456702, but the stock is measured on 1324657 and therefore the two components is not available after using

Left keep(StockCompare)

So I need something that says

Left keep(StockCompare) where left(ArticleID,7)

Then I think it will give me 123456701 and 123456702 since (left,7) = 1234567

Hope somebody can help

best regards

Bjarne

[its_anandrjs]

Hi,

In DCU table you use where condition on that add a condition

Where Left( ArticleID , 7 )

HTH

Rgds

Anand

[#3]

Hi

Do you know where in the statement I have to put it in. I have tried different spots know without any luck.

regards

Bjarne

[#4 · Accepted solution]

Hello Bjarne,

I would dupclicate the key field and create an additional  surrogato key only for technical reasons of linking the tables. This would be similar to this:

StockCompare:

LOAD

Left("MATERIAL_ID", 7) as $Article_ID; // key for linking

. . .

Left keep(StockCompare)
LOAD
. . .

COLLIWEIGHT/1000 as Weight,

"MATERIAL_ID" as ArticleID,

Left("MATERIAL_ID", 7) as $Article_ID; // key for linking

;

HtH

Roland

[#5]

Hi

Thank you Sir's