Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
HirisH_V7
Master
Master

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
sunny_talwar

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;

View solution in original post

7 Replies
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
Master
Master
Author

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!”
sunny_talwar

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

HirisH_V7
Master
Master
Author

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

HirisH
“Aspire to Inspire before we Expire!”
sunny_talwar

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
Champion
Champion

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

settu_periasamy
Master III
Master III

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