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

Picking Date From Excel Header and Make it as a Field

HirisH_V7 — Mon, 22 Feb 2016 03:52:03 GMT

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

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

sunny_talwar — Mon, 22 Feb 2016 03:58:37 GMT

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;

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

HirisH_V7 — Mon, 22 Feb 2016 04:08:38 GMT

Hi Sunny,

Thanks,

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

-Hirish

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

sunny_talwar — Mon, 22 Feb 2016 04:10:15 GMT

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

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

HirisH_V7 — Mon, 22 Feb 2016 04:13:17 GMT

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

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

sunny_talwar — Mon, 22 Feb 2016 04:16:22 GMT

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

tamilarasu — Mon, 22 Feb 2016 04:29:46 GMT

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

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

settu_periasamy — Mon, 22 Feb 2016 04:48:32 GMT

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;