Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
HirisH_V7
Honored Contributor

Picking Date From Excel Header and Make it as a Field

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

HirisH
“Aspire to Inspire before we Expire!”
1 Solution

Accepted Solutions

Re: Picking Date From Excel Header and Make it as a Field Column

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;

7 Replies

Re: Picking Date From Excel Header and Make it as a Field Column

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
Honored Contributor

Re: Picking Date From Excel Header and Make it as a Field Column

Hi Sunny,

Thanks,

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

-Hirish

HirisH
“Aspire to Inspire before we Expire!”

Re: Picking Date From Excel Header and Make it as a Field Column

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

HirisH_V7
Honored Contributor

Re: Picking Date From Excel Header and Make it as a Field Column

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

HirisH
“Aspire to Inspire before we Expire!”

Re: Picking Date From Excel Header and Make it as a Field Column

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;

Re: Picking Date From Excel Header and Make it as a Field Column

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') ;

Capture.PNG

Re: Picking Date From Excel Header and Make it as a Field Column

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;

Capture.JPG