load data to 1 string per item

[Anonymous]

Hi guys

Im sure you can help on this matter quite quickly.

I have a dimension table which includes some conversion factors (Box, Layer, Pallet) by item. Now i would like to join the data in the loading script to one string by item, which includes LAqty, MKqty, Palqty.

Actual tabelbox result

wished result suppose to be like this

%Item	LAqty	MKqty	PALqty
M86111476	200	10	400

Many thansk

[Peter_Cammaert]

Convert to straight table and use Sum(YourAmountOrQuantity) in each expression column to get the required result. QlikView will automatically group rows as long as you keep one dimension column (in this case %Item)

[sunny_talwar]

May be like this in the script:

LOAD %Item,

          Sum(If(Len(Trim(LAqty)) = 0, 0, LAqty)) as LAqty,

          Sum(If(Len(Trim(MKqty)) = 0, 0, MKqty)) as MKqty,

          Sum(If(Len(Trim(PALqty)) = 0, 0, PALqty)) as PALqty

FROM Source

Group By %Item;

[Peter_Cammaert]

Hi Sunny, why the if-len-trim constructs? Sum() will automatically ignore NULL-values.

[sunny_talwar · Accepted solution]

Oh ya, you are right, I got confused between Avg and Sum. One of those functions is not able to give an output when its null. I thought it was sum, but you are right, its actually Avg which won't work here

So may be this:

LOAD %Item,

          Sum(LAqty) as LAqty,

          Sum(MKqty) as MKqty,

          Sum(PALqty) as PALqty

FROM Source

Group By %Item;

[Anonymous]

Many thanks guys, it works

Rgs Rob