apply map for fact details

[chaitanyajami]

hi,

actually i have order id,soap id,number of items sold ,year wise in ORDER table and soap id,cost in SOAP DETAILS table , now i want to derive a new field for number of items sold *  cost, i have used apply map to get cost column into my fact table(ORDER) but the multiplication is not working please help

MapOrdertoSoap:

mapping Load

[SOAP ID],

COST

from

(ooxml, embedded labels, table is [SOAP DETAILS]);

ORDERS:

LOAD ID as orderid,

     [SOAP ID],

     [CITY ID],

     [NO OF ITEMS SOLD],

     [SALE YEAR],

     ApplyMap('MapOrdertoSoap','[SOAP ID]','NO DATA') as COST,

     [NO OF ITEMS SOLD] * COST as [ORDER AMOUNT]

     FROM

     (ooxml, embedded labels, header is 2 lines, table is ORDERS);

[sunny_talwar · Accepted solution]

[SOAP ID] doesn't need single quotes around it and you would need to put the ORDER AMOUNT calculation in a preceding load. Try this:

MapOrdertoSoap:

mapping Load

[SOAP ID],

COST

from

(ooxml, embedded labels, table is [SOAP DETAILS]);

ORDERS:

LOAD *,

          [NO OF ITEMS SOLD] * COST as [ORDER AMOUNT];

LOAD ID as orderid,

     [SOAP ID],

     [CITY ID],

     [NO OF ITEMS SOLD],

     [SALE YEAR],

    ApplyMap('MapOrdertoSoap', [SOAP ID],'NO DATA') as COST

     FROM

     (ooxml, embedded labels, header is 2 lines, table is ORDERS);

[chaitanyajami]

Hi Sunny,

Thanks a lot, its working fine.

[sunny_talwar]

Awesome