topic Re: load data to 1 string per item in QlikView

load data to 1 string per item

Anonymous — Thu, 24 Dec 2015 10:43:05 GMT

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

Re: load data to 1 string per item

Peter_Cammaert — Thu, 24 Dec 2015 11:18:53 GMT

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)

Re: load data to 1 string per item

sunny_talwar — Thu, 24 Dec 2015 11:34:37 GMT

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;

Re: load data to 1 string per item

Peter_Cammaert — Thu, 24 Dec 2015 11:36:51 GMT

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

Re: load data to 1 string per item

sunny_talwar — Thu, 24 Dec 2015 11:39:20 GMT

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;

Re: load data to 1 string per item

Anonymous — Thu, 24 Dec 2015 12:19:35 GMT

Many thanks guys, it works

Rgs Rob