topic Re: Error in loading in QlikView

Error in loading

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

Hello everyone,

This is my script:

for Each vFile in FileList('C:\REAL_*.xlsx')

Files:
LOAD subfield('$(vFile)','\',-1) as FileName,
date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month,
'$(vFile)' as Path
AutoGenerate 1;

Files_needed:
NoConcatenate
first 2 LOAD *
Resident Files
Order by Month desc;

DROP Table Files;

Data:
LOAD * Inline [
Temp ];

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

DROP Table Files_needed;

DROP Field Temp;

It is supposed to load the two latest files, I have files named Real_DDMMYYYY. But it is considering that the file Real_31082020 is latest that Real_11092020 which is not true.
Can you please help me with that, I have a deadline in two days.
Thank you

Re: Error in loading

rubenmarin — Thu, 24 Sep 2020 07:05:14 GMT

Hi, I think you should change

date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month

date(date#(mid('_31082020',index('_31082020','_',-1)+1,8),'DDMMYYYY')) as FileDate

And use FileDate instead of Month in the next script sentences, it you only use Month you will get an isue when january-2021 comes.

But still I don't know why it sets august higher than september in your code.

Re: Error in loading

KaisGarci — Thu, 24 Sep 2020 07:15:29 GMT

Thanks for your help Mr @rubenmarin. But I want it to load automatically the two latest file. In your example, it will not do that.

Re: Error in loading

Kushal_Chawda — Thu, 24 Sep 2020 09:37:42 GMT

@KaisGarci try below

for Each vFile in FileList('C:\REAL_*.xlsx')

Files:
LOAD subfield('$(vFile)','\',-1) as FileName,
date(date#(trim(mid('$(vFile)',index('$(vFile)','_',-1)+1)),'DDMMYYYY')) as Date,
'$(vFile)' as Path
AutoGenerate 1;

Files_needed:
NoConcatenate
first 2 LOAD *
Resident Files
Order by Date desc;

DROP Table Files;

Data:
LOAD * Inline [
Temp ];

for i=1 to FieldValueCount('Date')

let vFilePath = FieldValue('Path',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

DROP Table Files_needed;

DROP Field Temp;

Re: Error in loading

KaisGarci — Thu, 24 Sep 2020 11:05:10 GMT

It gave me nothing Mr. @Kushal_Chawda .
I made some modifications, it is loading correctly but the variable MONTH as seen here

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

let vMonth = FieldValue('Month',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName,

'$(vMonth)' as MONTH
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

is not working correctly. When I do max (MONTH) it gives me either 31/08/2020 instead of 09/11/2020 or a number 44086

Re: Error in loading

KaisGarci — Thu, 24 Sep 2020 11:38:45 GMT

I think that the problem is with the variable MONTH, but when I have 31082020 and 10072020 it gives me that 31082020 but in the case of 31082020 and 10092020 it gives me also 31082020 in the max(MONTH)

Re: Error in loading

marcus_sommer — Thu, 24 Sep 2020 11:40:44 GMT

I suggest to consider seriously to change the filename-pattern from DDMMYYYY to YYYYMMDD which would avoid any measures to transform it during the load-statements. This may sound a bit ugly but from a practicable point of view it's often the fastest way - I did something similar already multiple times, ok. not with daily data else yearmonth and to change 2 - 5 years needs just 2 - 3 minutes.

With it you will keep your script much more readable and also get a proper sorting within the explorer - if the creation-date of the files is continuous you may sort for it but then you could use filetime() to get the latest files instead of evaluating the filenames.

Nevertheless if you want to keep by your origin approach you need to transform the date within the filename to the above mentioned structure of YYYYMMDD by ensuring that these aren't dates (are always only a formatting for the underlying number - on which functions like max() would be performed) else pure numbers.

- Marcus

Re: Error in loading

KaisGarci — Thu, 24 Sep 2020 11:51:42 GMT

Hello Mr. @marcus_sommer . It is loading perfectly, but the issue is with the variable MONTH (as you can see in this script)

let vFilePath = FieldValue('Path',$(i));

let vMonth = FieldValue('Month',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName,

'$(vMonth)' as MONTH

It gives that 08 August is bigger than 11 September

Re: Error in loading

Kushal_Chawda — Thu, 24 Sep 2020 12:03:32 GMT

@KaisGarci what is the value in vMonth variable?

Re: Error in loading

KaisGarci — Thu, 24 Sep 2020 12:07:19 GMT

It is the date variable whic is based on the two files, so it gives me to values of the latest dates. But it tells me that 31 august is latest than 11 september, when I tried to change the name to Real_10082020 (10<11) it gives me that 11 september is latest than 31 august in this case.

Re: Error in loading

KaisGarci — Thu, 24 Sep 2020 12:21:12 GMT

But the trick of Mr. @marcus_sommer of YYYYMMDD worked perfectly. Thank you Mr. @marcus_sommer

Re: Error in loading

rubenmarin — Fri, 25 Sep 2020 06:28:24 GMT

Hi, just to comment that I was curious of why you said that it's not working, I made 3 excel samples (31082020, 01092020 and 02092020) and it worked, retrieving only the 2 last files. Sample attached

Other than that, if you can rename the files as Marcus said has other benefits, so it's better to keep that way, but remember to include the year in the code, don't take only month and day, or in january you will find that the code is keeping the december files because the month is higher.

Re: Error in loading

KaisGarci — Fri, 25 Sep 2020 15:03:09 GMT

Hello Mr. @rubenmarin ,

Thank you for your response. Yes, the script, as you can see, is takig into consideration the whole date.

And the problem with the old format (DDMMYYYY) is that when I created a variable of the date called DATE, when I do max(DATE) it gives me the 31082020 instead of 11092020. But, with the new format (YYYYMMDD) it worked perfectly.
Thank you again for your help.