Using New Field after CrossTable

[ShannaJJ27]

Hi There,

I have the following data

Sales:
LOAD
FinYear,
Month,
Year,
PackSize,
SalesQty
FROM..

SellingPrice:
CrossTable(PackSize, Price, 2)
LOAD
'2016' AS FinYear,
Brand,
"1L",
"2L",
"500ml",
"250ml"
FROM..

Left Join(Sales)
Load * Resident SellingPrice;

Drop table SellingPrice;

I now need to Take the new "Price" column I created in the CrossTable and Multiply it by the SalesQty to create a new column in Sales Table "SalesRev"

Can any one help me please

[petter]

I think this should do the trick for you:

SalesRev:
LOAD
  *,
  Price*SalesQty AS SalesPrice
RESIDENT
  SellingPrice;

DROP TABLE SellingPrice;

Please click LIKE if this is helpful as that is the only way to give me any credit on this forum... 🙂

[Vegar]

TmpSales:
LOAD
  FinYear,
  Month,
  Year,
  PackSize,
  SalesQty
FROM..

SellingPrice:
CrossTable(PackSize, Price, 2)
LOAD
  '2016' AS FinYear,
  Brand,
  "1L",
  "2L",
  "500ml",
  "250ml"
FROM..

Left Join(TmpSales)
Load * Resident SellingPrice;

Drop table SellingPrice;

Sales:
NoConcatenate
Load 
 FinYear,
  Month,
  Year,
  PackSize,
  SalesQty,
  Price
  Price * SalesQty as [SalesRev]
Resident TmpSales;
Drop table TmpSales;

[ShannaJJ27]

I get an error with this saying SalesQty not found

[Vegar]

Please check the content of TmpSales when you put an Exit Script after the
Drop table SellingPrice;

The field should be present in that table. If not then check your script
and field name typing errors.