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

[MarcoWedel]

Hi,

a more generic solution without hard coded source column names (i.e. no changes for an additional 2017 or "no of suppliers" column required) could be:

tabTemp1:
CrossTable (ColNam, ColVal, 5)
LOAD RecNo() as ID, * FROM [https://community.qlik.com/servlet/JiveServlet/download/1101274-240175/Siv.xlsx] (ooxml, embedded labels, table is [Master Data]);

TabTemp2:
Generic
LOAD ID,
    Right(ColNam,4) as Year,
    Left(ColNam,Len(ColNam)-5),
    ColVal
Resident tabTemp1;

DROP Fields ColNam, ColVal From tabTemp1;

TabMasterData:
NoConcatenate
LOAD Distinct * Resident tabTemp1;

DROP Table tabTemp1;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'TabTemp2.*') THEN
  LEFT JOIN (TabMasterData) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

hope this helps

regards

Marco