Transformation Problem

[Anil_Babu_Samineni]

Hello,

I am trying to load the excel with Transpose. But, Unfortunately transpose is not working. Here, I want to calculate for my Bar Graph which is Dim as Months(Jan ....Dec) And i want to calculate each month of the sales data

If Jan then i need to create three expressions one for 16, 17 & 18

Please help me

[jonathandienst]

If you want help with a load problem, I suggest that you upload sample source data as well. But it sounds like you might need a crosstable load rather than a transpose.

[Anil_Babu_Samineni]

Jonathan, Thanks for your Reply

PFA.

Now, after that i want to show the data Dimension is MMM format and the expression should be 2016, 2017 & 2018 Data

Like I want to know each month how much sales they are spending for each year

- ANIL

[jonathandienst · Accepted solution]

You sample XLSX does not have the real labels in columns 1-8. You can use this script:

T_Production_data:

CrossTable(T_MonthYear, Sales, 😎

LOAD A as Plant,

     B as [Aranda PTC],

     C as Material,

     D as Product_Name,

     E as [Pr Glob Brand],

     F as [Base Unit],

     G as Type,

     H as [Cal. year / month],

     [01.2016],

     [02.2016],

     [03.2016],

     [04.2016],

     [05.2016],

     [06.2016],

     [07.2016],

     [08.2016],

     [09.2016],

     [10.2016],

     [11.2016],

     [12.2016],

     [01.2017],

     [02.2017],

     [03.2017],

     [04.2017],

     [05.2017],

     [06.2017],

     [07.2017],

     [08.2017],

     [09.2017],

     [10.2017],

     [11.2017],

     [12.2017],

     [01.2018],

     [02.2018],

     [03.2018],

     [04.2018],

     [05.2018],

     [06.2018],

     [07.2018],

     [08.2018],

     [09.2018],

     [10.2018],

     [11.2018],

     [12.2018]

FROM

(ooxml, embedded labels, table is Y0ANALYSIS_PATTERN);

Production_data:

LOAD *,

  Month(MonthYear) as [Production Month],

  Year(MonthYear) as [Production Year]

;

LOAD *,

  Date(Date#(T_MonthYear, 'MM.yyyy')) as MonthYear

Resident T_Production_data;

DROP Table T_Production_data;

[Anil_Babu_Samineni]

Thanks,

I forget this

Date(Date#(T_MonthYear, 'MM.yyyy')) as MonthYear