Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
please find the my attached source files.
I have source1 folder Three Qvds names like
2016_01_01.qvd
2016_01_02.qvd
2016_01_03.qvd
qvd creation time is 11:10:10 AM
and
source2 folder Three Qvds names like
2016_01_01.qvd
2016_01_04.qvd
2016_01_05.qvd
Qvd creation time is 11:15:15 AM
then i have loaded all qvd's into qlikview .Check here 2016_01_01.qvd name having 2 qvd's ..
coming to my requirement
i need latest with same name 2016_01_01.qvd from source2
Output: i want 2016_01_01.qvd from source2 because this is the latest qvd based on timestamp.
Hi Kushal,
Thanks for your support.
i got the output. i have used this code
for each Vpeek in FileList('H:\Personal\LATEST QVD\source1\*.xlsx');
let VRep=Replace(Vpeek,'.xlsx','.qvd');
T1:
LOAD*
FROM
[$(Vpeek)]
(ooxml, embedded labels, table is Sheet1);
STORE T1 into $(VRep);
drop table T1;
next
for each Vpeek1 in FileList('H:\Personal\LATEST QVD\source2\*.xlsx');
let VRep1=Replace(Vpeek1,'.xlsx','.qvd');
T4:
LOAD *
FROM
[$(Vpeek1)]
(ooxml, embedded labels, table is Sheet1);
STORE T4 into $(VRep1);
drop table T4;
next
LET vSource1Path ='H:\Personal\LATEST QVD\source1\';
LET vSource2Path ='H:\Personal\LATEST QVD\source2\';
LET vStorePath ='H:\Personal\LATEST QVD\';
For each vFile1 in Filelist ('$(vSource1Path)*.qvd')
Files1:
LOAD SubField('$(vFile1)','\',-1) as FileName,
'$(vFile1)' as FileName1,
filetime('$(vFile1)') as FileTime1
autogenerate 1;
Next vFile1
For each vFile2 in Filelist ('$(vSource2Path)*.qvd')
Files2:
LOAD SubField('$(vFile2)','\',-1) as FileName,
'$(vFile2)' as FileName2,
filetime('$(vFile2)') as FileTime2
autogenerate 1;
Next vFile2
New:
noconcatenate
LOAD *
Resident Files1;
inner join(New)
LOAD *
Resident Files2;
Drop Tables Files1, Files2;
FileName:
LOAD *
Where not IsNull(FileName);
LOAD
if(FileTime2 > FileTime1,FileName2,FileName1) as FileName,
timestamp(Rangemax(FileTime2,FileTime1)) as FileTime
Resident New;
DROP Table New;
AllQVD:
LOAD 1 as Junk
Autogenerate 1;
For i=1 to FieldValueCount('FileName')
let vFileName = FieldValue('FileName',$(i));
Concatenate(AllQVD)
LOAD *,
'$(vFileName)' as FileName
FROM $(vFileName)(Qvd);
Next i
drop field Junk;
Store AllQVD into $(vStorePath) FinalQVD.qvd;
Regards,
Mahesh.
load all file and create 2 field as
1- file name
2 file time
Returns a string containing the name of the table file currently being read, without path but including the extension.
Example:
Load *, filename( ) as X from
C:\UserFiles\abc.txt
Will return 'abc.txt' in field X in each record read.
Returns a timestamp for the date and time of the last modification of the file filename. If no filename is specified, the function will refer to the currently read table file.
Examples:
filetime( 'xyz.xls' )
Will return the timestamp of the last modification of the file xyz.xls.
Load *, filetime() as X from abc.txt ;
Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.
Hello Mahesh,
Here is a sample script for you:
For each File in FileList('C:Source1\*')
Sources:
//Source1:
LOAD
'$(File)' as File,
FileName() as File_Name,
FileTime('$(File)') as File_Time
AutoGenerate (1);
next
For each File in FileList('C:\Source2\*')
//Source2:
LOAD
'$(File)' as File,
FileName('$(File)') as File_Name,
FileTime('$(File)') as File_Time
AutoGenerate (1);
next
EXIT Script;
result:
load File_Name,
max(File_Time) as Max_File_Time
Resident Sources
group by File_Name;
hope this helps
Hi Florentina,
can you share Example with my data?
Regards,
Mahesh
try below code
For each vFile1 in Filelist ('PathSource1\*.qvd')
Files1:
LOAD filebasename('$(vFile1)') as FileName,
'$(vFile1)' as FileName1,
filetime('$(vFile1)') as FileTime1
autogenerate 1
Next vFile1
For each vFile2 in Filelist ('PathSource2\*.qvd')
Files2:
LOAD filebasename('$(vFile2)') as FileName,
'$(vFile2)' as FileName2,
filetime('$(vFile2)') as FileTime2
autogenerate 1
Next vFile2
New:
noconcatenate
LOAD *
Resident Files1;
join(New)
LOAD *
Resident Files2;
Drop Tables Files1, Files2;
Final:
noconcatenate
LOAD if(FileTime2 > FileTime1 or isnull(FileTime1),FileName2,
if(FileTime1 > FileTime2 or isnull(FileTime2),FileName1)) as FileName,
Rangemax(FileTime2,FileTime1) as FileTime
Resident New;
drop table New;
AllQVD:
LOAD 1 as Junk
Autogenerate 1;
For i=1 to FieldValueCount('FileName')
let vFileName = FieldValue('FileName',$(i));
Concatenate(AllQVD)
LOAD *,
'$(vFileName)' as FileName
FROM $(vFileName)(Qvd);
Next i
drop field Junk;
Store AllQVD into Path\FinalQVD.qvd;
Hi Kushal,
can you attach QVW ?
I don't have QlikView in my system. Can you try this code at your end?
Hi Kushal,
i am getting error "TABLE NOT FOUND NEW"
regards,
mahesh
Are you using the same code? can you show me the screenshot of your code?
Hi Kushal,
FOR Each source1 in FileList('H:\Personal\TOP5\source1\QVDS\*.qvd')
source1:
load FileBaseName('$(source1)') as filename,
'$(source1)' as filename1,
FileTime('$(source1)') as FileTime1 AutoGenerate 1;
next source1
FOR Each source2 in FileList('H:\Personal\TOP5\source1\QVDS\*.qvd')
source2:
load FileBaseName('$(source1)') as filename,
'$(source1)' as filename2,
FileTime('$(source1)') as FileTime2 AutoGenerate 1;
next source2
New:
noconcatenate
LOAD *
Resident source1;
join(New)
LOAD *
Resident source2;
Drop Tables source1, source2;
Final:
noconcatenate
LOAD if(FileTime2 > FileTime1 or isnull(FileTime1),FileTime2,
if(FileTime1 > FileTime2 or isnull(FileTime2),FileTime1)) as filename,
Rangemax(FileTime2,FileTime1) as FileTime
Resident New;
drop table New;
AllQVD:
LOAD 1 as Junk
Autogenerate 1;
For i=1 to FieldValueCount('filename')
let vFileName = FieldValue('filename',$(i));
Concatenate(AllQVD)
LOAD*,
'$(vFileName)' as filename
FROM $(vFileName)(Qvd);
Next i
drop field Junk;
Store AllQVD into FinalQVD.qvd;
NOTE:- Here I am getting Error vFileName is showing error and filetime,filename is showing like this