Date column from header of excel

[#1]

Hi All,

    [Business Group],

     [Directorate-Code],

     [Directorate-Name],

     Region,

    [No of Employees 2014],

     [No of Employees 2015],

     [No of Employees 2016],

     [No of Customers 2014],

     [No of Customers 2015],

     ([No of Employees 2014],

     [No of Customers 2016],

                                        as date

i need to get Date column from hear

any idea if i add 2017 also it should pick .

Thanks

Sivaram

[vinieme12]

i think you need a crosstab load, can you provide a sample of what your are looking at?

[avinashelite]

it will be 2 step process

1. apply crosstable and transform the columns

Temp:

Crosstable(Date,Value,5)

LOAD [Business Group],

     [Directorate-Code],

     [Directorate-Name],

     Region,

    [No of Employees 2014],

     [No of Employees 2015],

     [No of Employees 2016],

     [No of Customers 2014],

     [No of Customers 2015],

     ([No of Employees 2014],

     [No of Customers 2016]

NOCONCATENATE

Result:

LOAD *,

right(Date,4) as Year_date

resident

Temp;

Drop table Temp;

[#4]

This is sample data

[#5]

For Employee is is picking but not Customers

using year filter i need to

[Business Group],

     [Directorate-Code],

     [Directorate-Name],

     Region,

     [No of Employees 2014],

     [No of Employees 2015],

     [No of Employees 2016],

     [No of Customers 2014],

     [No of Customers 2015],

     [No of Customers 2016]

using NOCONCATENATE

if i select year=2014 employees 2014 and customers 2014 should come

[#6]

Hi Avinash,

any update...

Regards

Sivaram.

[vinieme12]

See Attached

EMPTEMP:

CrossTable(Period, EmpCount, 4)

LOAD [Business Group],

     [Directorate-Code],

     [Directorate-Name],

     Region,

     [No of Employees 2014],

     [No of Employees 2015],

     [No of Employees 2016]

FROM

(ooxml, embedded labels, table is [Master Data]);

FACT:

LOAD [Business Group],

     [Directorate-Code],

     [Directorate-Name],

     Region,

     right(Period,4) as YEarField,

     [Business Group]&[Directorate-Code]&[Directorate-Name]&Region&right(Period,4) as KeyField,

     EmpCount

RESIDENT EMPTEMP ;

DROP TABLE EMPTEMP;

CustTEMP:

CrossTable(Period, CustCount, 4)

LOAD [Business Group],

     [Directorate-Code],

     [Directorate-Name],

     Region,

     [No of Customers 2014],

     [No of Customers 2015],

     [No of Customers 2016]

FROM

(ooxml, embedded labels, table is [Master Data]);

LEFT JOIN(FACT)

LOAD

     [Business Group]&[Directorate-Code]&[Directorate-Name]&Region&right(Period,4) as KeyField,

     CustCount

RESIDENT CustTEMP ;

DROP TABLE CustTEMP;

[Mark_Little]

Hi

Format:

CrossTable(Date, NumberEmps, 4)

LOAD [Business Group],

    [Directorate-Code],

    [Directorate-Name],

    Region,

    [No of Employees 2014],

    [No of Employees 2015],

    [No of Employees 2016],

    [No of Customers 2014],

    [No of Customers 2015],

    [No of Customers 2016]

FROM

[Siv.xlsx]

(ooxml, embedded labels, table is [Master Data]);

NoConcatenate

DATA:

LOAD

  [Business Group],

    [Directorate-Code],

    [Directorate-Name],

    Region,

    NumberEmps,

    RIGHT(Date,4) as Year

Resident Format;

Drop Table Format;

!

[avinashelite]

It should work, your talking about the Customers column in the Master data sheet or other sheets ? if its other sheet then you need to convert the other sheet data using the above code and need to link with the proper keys ..

[vinieme12]

sivasrava have you tried the above?