Recent qvd

[qlikview979]

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.

[florentina_doga]

load all file and create 2 field as

1- file name

2 file time

filename()

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.

filetime([ filename ])

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.

[zhadrakas]

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

[qlikview979]

Hi Florentina,

can you share Example with my data?

Regards,

Mahesh

[Kushal_Chawda]

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;

[qlikview979]

Hi Kushal,

can you attach QVW ?

[Kushal_Chawda]

I don't have QlikView in my system. Can you try this code at your end?

[qlikview979]

Hi Kushal,

i am getting error "TABLE NOT FOUND NEW"

regards,

mahesh

[Kushal_Chawda]

Are you using the same code? can you show me the screenshot of your code?

[qlikview979]

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