Help on -> getting the correct value through grouping or set analysis?

[#1]

ITEM	COMPANY	BRAND	ON-HAND-QTY	CTN
item2	comp1	A	20	10
item3	comp1	A	45	5
item4	comp1	A	70	15
item1	comp1	A	30	20
item1	comp2	A	25	20

Above is the current result & the below is the excel file.

ITEM	COMPANY	CTN
item1	comp1	12
item1	comp2	20
item2	comp1	10
item3	comp1	5
item4	comp1	15

I wanted to make the result to become something like this...

Header 1	Header 2	Header 3	Header 4	Header 5
item1	comp1	A	30	12
item1	comp2	A	25	20

I have try,

Max({<QUANTITY_UM={"CTN"}, ITEM, COMPANY>}CONVERSION_QTY)

//sum(if (QUANTITY_UM='CTN', CONVERSION_QTY, 0) )

//if (QUANTITY_UM='CTN', Sum(CONVERSION_QTY), 0)

but the result is not what I want. Please help.

[Anonymous]

What is the difference b/w these two tables??

For me both are same except two more columns in above table, Brand, ONHandQty

any other difference?

If both are two different tables, try to concatenate or Join()

[Anonymous]

Hello,

Header4 = sum({<Header1={'item1'}>}Header4)

Header5 = sum({<Header1={'item1'}>}Header5)

BR

Serhan

[jagan]

Hi,

Try like this

Data:

LOAD

*

FROM Table1;

Left Join(Data)

LOAD

ITEM,

COMPANY,

CTN AS CTN_NEW

FROM Table2;

Now in chart use like below

Dimension: ITEM, COMPANY, BRAND

Expression: Sum([ON-HAND-QTY]), Sum(CTN_NEW)

Hope this helps you.

Regards,

Jagan.