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.
Can you comment this Store command and show the Data model by pressing ctrl+t?
I have Commented the store command then
Front End is like.... and data model also....
Hi Kushal,
My Script is like,
FOR Each source1 in FileList('H:\Personal\TOP5\source1\QVDS\*.qvd')
source1:
load FileName('$(source1)') as filename,
'$(source1)' as filename1,
FileTime('$(source1)') as FileTime1 AutoGenerate 1;
next source1
FOR Each source2 in FileList('H:\Personal\TOP5\source2\QVDS\*.qvd')
source2:
load FileName('$(source2)') as filename,
'$(source2)' as filename2,
FileTime('$(source2)') as FileTime2 AutoGenerate 1;
next source2
EXIT Script;
New:
noconcatenate
LOAD *
Resident source1;
join(New)
LOAD *
Resident source2;
Drop Tables source1, source2;
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 FinalQVD.qvd;
NOTE:-I am going to reload noting is coming in front end.
Hi,
Sorry COMMENT the "EXIT SCRIPT"
Hi Kushal,
Sorry,not showing any error.
it is perfectly. but here main problem is i am not getting latest qvd data
i am getting source1 folder qvd data but
i need here source 2 folder common qvd data.
Regards,
Mahesh.
Hi,
Source1 QVD's names like
2016_04_21.qvd
2016_04_22.qvd
2016_04_23.qvd
and QVD Creation time is 11:10:15
Source2 QVD's names like
2016_04_21.qvd
2016_04_24.qvd
2016_04_25.qvd
and QVD Creation time is 12:10:15
here we are having same name (2016_04_21.qvd) qvd in source1 and source2 based on time source2 qvd's are
latest.i want only (single)2016_04_21.qvd from source2 remaining qvds are not required.
Regards,
Mahesh
try to change this.
Final:
noconcatenate
LOAD
(if(frac(FileTime2) > frac(FileTime1) ,filename2,filename1)) as filename,
timestamp(Rangemax(FileTime2,FileTime1)) as FileTime
Resident New;
Hi Kushal,
I have made the changes in script. now also i am getting source1 qvds data.
but i need source2 in 2016_04_21.qvd only.because that is latest one compare to source1 folder .
Regards,
Mahesh
Can you show me filetime1 and filetime2 values. I think both are same that is why it is picking from source1 only
Yes, you are right showing like this,
FOR Each source1 in FileList('H:\Personal\LATEST QVD\source1\QVDS\*.qvd')
source1:
load FileName('$(source1)') as filename,
'$(source1)' as filename1,
FileTime('$(source1)') as FileTime1 AutoGenerate 1;
next source1
FOR Each source2 in FileList('H:\Personal\LATEST QVD\source1\QVDS\*.qvd')
source2:
load FileName('$(source2)') as filename,
'$(source2)' as filename2,
FileTime('$(source2)') as FileTime2 AutoGenerate 1;
next source2
//New:
//noconcatenate
//LOAD *
//Resident source1;
//join(New)
//LOAD *
//Resident source2;
// Drop Tables source1, source2;
//
//Final:
//
//Final:
//noconcatenate
//LOAD
//(if(frac(FileTime2) > frac(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 FinalQVD.qvd;