Left keep function

Thu, 06 Oct 2011 12:55:23 GMT

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

Left keep function

its_anandrjs — Thu, 06 Oct 2011 13:07:05 GMT

Hi,

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

Where Left( ArticleID , 7 )

HTH

Rgds

Anand

Left keep function

Fri, 07 Oct 2011 06:44:19 GMT

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

regards

Bjarne

Left keep function

Fri, 07 Oct 2011 08:01:18 GMT

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

Left keep function

Fri, 07 Oct 2011 08:08:09 GMT

Thank you Sir's

topic Left keep function in QlikView

Left keep function

Left keep function

Left keep function

Left keep function

Left keep function