Discussion Board for collaboration related to QlikView App Development.
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
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;
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;
Hi Sunny,
Thanks,
Is there any way to automate it. If every time i will get a new file with same format .
-Hirish
Are you talking about being able to loop through multiple files?
yes sunny, I have to pick date column from each excel file when it is loading.
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;
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') ;
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;