topic Re: Recent qvd in QlikView

Recent qvd

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Recent qvd

florentina_doga — Tue, 03 May 2016 07:07:32 GMT

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.

Re: Recent qvd

zhadrakas — Tue, 03 May 2016 07:28:10 GMT

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

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 07:39:33 GMT

Hi Florentina,

can you share Example with my data?

Regards,

Mahesh

Re: Recent qvd

Kushal_Chawda — Tue, 03 May 2016 08:15:22 GMT

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;

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 08:49:01 GMT

Hi Kushal,

can you attach QVW ?

Re: Recent qvd

Kushal_Chawda — Tue, 03 May 2016 08:53:51 GMT

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

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 08:55:26 GMT

Hi Kushal,

i am getting error "TABLE NOT FOUND NEW"

regards,

mahesh

Re: Recent qvd

Kushal_Chawda — Tue, 03 May 2016 09:03:06 GMT

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

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 09:48:52 GMT

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

Re: Recent qvd

Kushal_Chawda — Tue, 03 May 2016 10:00:45 GMT

Couple of changes

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

Re: Recent qvd

Kushal_Chawda — Tue, 03 May 2016 10:01:43 GMT

Also can you show me, what are the Values you are getting in filename1 and filename2 fields

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 10:08:59 GMT

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 10:17:36 GMT

Hi Kushal,

I have made the changes Now also i am getting same problem like first screen shot.

Regards,

Mahesh.

Re: Recent qvd

qlikview979 — Tue, 03 May 2016 11:46:14 GMT

Hi Experts,

can any one give solution for my requirement

Regards,

Mahesh

Re: Recent qvd

zhadrakas — Tue, 03 May 2016 11:54:36 GMT

Hello mahesh,

please upload your qvw.

Re: Recent qvd

Kushal_Chawda — Wed, 04 May 2016 05:59:31 GMT

what is the error?

Re: Recent qvd

qlikview979 — Wed, 04 May 2016 06:08:59 GMT

Hi Kushal,

I am using your script only with modifications,

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

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;

Re: Recent qvd

Kushal_Chawda — Wed, 04 May 2016 06:13:26 GMT

change this if statement. You are taking filetime instead of filename.

(if(FileTime2 > FileTime1 or isnull(FileTime1),filenam1,

if(FileTime1 > FileTime2 or isnull(FileTime2),filename2)))as filename,

(if(FileTime2 > FileTime1 or isnull(FileTime1),filenam2,

if(FileTime1 > FileTime2 or isnull(FileTime2),filename1)))as filename,

update: Corrected if

Re: Recent qvd

qlikview979 — Wed, 04 May 2016 06:23:01 GMT

Hi Kushal,

Now Showing this error,