Percentagewise calculation

[nachiket_shinde]

Hello Experts,

  Please find attached Excel for Inputs and required Output.

Please help me to calculate output.

Sheet1 shows that 201611 has dates from 10/30/2016 to 12/03/2016 and percentage for each day.

Sheet2 shows Forecast amount per LOB, country and date

Regards

[sunny_talwar]

May be like this:

Table:

CrossTable (DateLink, Percentage, 2)

LOAD [Country Code],

    Date,

    [42673],

    [42674],

    [42675],

    [42676],

    [42677],

    [42678],

    [42679],

    [42680],

    [42681],

    [42682],

    [42683],

    [42684],

    [42685],

    [42686],

    [42687],

    [42688],

    [42689],

    [42690],

    [42691],

    [42692],

    [42693],

    [42694],

    [42695],

    [42696],

    [42697],

    [42698],

    [42699],

    [42700],

    [42701],

    [42702],

    [42703],

    [42704],

    [42705],

    [42706],

    [42707]

FROM

[Percentage wise calculation.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD [Country Code],

    Date,

    Date(Num#(DateLink)) as DateLink,

    Percentage

Resident Table;

DROP Table Table;

Left Join (FinalTable)

LOAD Country as [Country Code],

    Currency,

    LOB,

    Date,

    [Forecast Amount]

FROM

[Percentage wise calculation.xlsx]

(ooxml, embedded labels, table is Sheet2);

FinalFinalTable:

LOAD *,

  [Forecast Amount] * Percentage as [Forecast USD]

Resident FinalTable

Where [Forecast Amount] * Percentage > 0;

DROP Table FinalTable;