Picking Date From Excel Header and Make it as a Field

[HirisH_V7]

Hi Qlikers,

Here in the below excel Files ,I can see The Date as a header ,I want to make that Date as a field column for that Data when it is loading.

Like DD/MM/YYY.

Please Find Attachments,

Thanks in advance,

Hirish

[sunny_talwar]

May be try this:

Table1:

LOAD Sno,

    Data

FROM

[Data1 (2).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Join (Table1)

First 1

LOAD Date(MonthStart(Date#(SubField(A, ': ', 2), 'MMM-YY')), 'MMM-YY') as Date

FROM

[Data1 (2).xlsx]

(ooxml, no labels, table is Sheet1);

Table2:

LOAD Sno,

    Data

FROM

Data2.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Join (Table2)

First 1

LOAD Date(MonthStart(Date#(SubField(A, ': ', 2), 'MMM-YY')), 'MMM-YY') as Date

FROM

Data2.xlsx

(ooxml, no labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD *

Resident Table1;

Concatenate (FinalTable)

LOAD *

Resident Table2;

DROP Table Table1, Table2;

[HirisH_V7]

Hi Sunny,

Thanks,

Is there any way to automate it. If every time i will get a new file with same format .

-Hirish

[sunny_talwar]

Are you talking about being able to loop through multiple files?

[HirisH_V7]

yes sunny, I have to pick date column from each excel file when it is loading.

[sunny_talwar · Accepted solution]

Try this:

FOR Each file in 'Data1 (2)', 'Data2'

  Table1:

  LOAD Sno,

      Data,

      '$(file)' as FileName

  FROM

  [$(file).xlsx]

  (ooxml, embedded labels, header is 1 lines, table is Sheet1);

  Table2:

  First 1

  LOAD Date(MonthStart(Date#(SubField(A, ': ', 2), 'MMM-YY')), 'MMM-YY') as Date,

      '$(file)' as FileName

  FROM

  [$(file).xlsx]

  (ooxml, no labels, table is Sheet1);

NEXT

Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

DROP Field FileName;

[tamilarasu]

Hi,

Check the below one.

Sub ScanFolder(Root)

For each FileExtension in 'xlsx'
   For each FoundFile in filelist( Root & '\*.' & FileExtension)

Date:  
      First 1 LOAD
         A as Date
      FROM [$(FoundFile)] (ooxml, explicit labels, table is Sheet1);
      Let vDate = SubField( Trim(Peek('Date',-1,'Date')), ': ', 2);
      Drop Table Date; 

Data:
     LOAD Sno,
     Data,
    '$(vDate)' as Date
     FROM [$(FoundFile)] (ooxml, embedded labels, header is 1 lines, table is Sheet1);
   Next FoundFile
Next FileExtension

End sub
Call ScanFolder('Files') ;

[settu_periasamy]

Hi,

May be One more solution using Enable transformation Wizard (If the Source in excel)

Directory;

LOAD      Num(@1) as Sno,
        Num(@2) as Data,
        Date(MonthStart(Date#(Replace(@3,'Date : ',''), 'MMM-YY')), 'MMM-YY') as MonthYear
FROM
Test\*.xlsx
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)), 0),
ColXtr(1, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)), 0),
Replace(3, top, StrCnd(null)),
Remove(Row, Pos(Top, 1))
)) Where RecNo()>1;