topic Re: File import problem in QlikView

File import problem

stevietm — Wed, 26 Apr 2017 11:31:01 GMT

Hi All,

I have a bit of a problem with my script where i want to pull only 2016 data for the current month. I have a set of 4 data files that i get daily. Exp DNI_Daily_RICA_YYYYMMDD, ERZ_Daily_RICA_YYY-MMDD, TSPC_Daily_RICA_YYYYMMDD and Tropical_Daily_RICA_YYYYMMDD.

All are txt files. See below script i use to import current month and last year same month.

File i import

LET vReportMonth = text(Date(Today()-3,'YYYYMM'));

LET vReportMonth2016 = text(Date(Today()-364,'YYYYMM'));

This script is the same for my 2017 import and it runs fine. Also i use this script on the other txt files for 2016 as well and it also runs fine. Its only on this file that i get the error.

LOAD

@5 as ICCID,

1 as RICAS,

alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS DATE_KEY,

alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS RicaDate,

Day( alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))) as Day

FROM

[..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_$(vReportMonth2016)*.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq);

Error i get

File not found error

Cannot open file: 'D:\QlikView\QVData\DNIRetailDaily\DNI_DAILY_RICA_201604*.txt'

System error: Filesystem::FindImpl FindFirst call: Incorrect function: "D:\QlikView\QVData\DNIRetailDaily"

Concatenate

LOAD

@5 as ICCID,

1 as RICAS,

alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS DATE_KEY,

alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS RicaDate,

@9 as Client,

Day( alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))) as Day

FROM

[..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_201604*.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq)

Thanks

Regards

STM

Re: File import problem

stabben23 — Wed, 26 Apr 2017 11:47:14 GMT

Hi,

if this is Your filename DNI_Daily_RICA_YYYY-MM-DD, this DNI_DAILY_RICA_201604*.txt wont match.

Maybe keep the format in Your variable:

LET vReportMonth2016 = text(Date(Today()-364,'YYYY-MM'));

Re: File import problem

stevietm — Wed, 26 Apr 2017 11:51:44 GMT

Sorry all file names are YYYYMMDD with no dash.

Re: File import problem

stabben23 — Wed, 26 Apr 2017 12:03:21 GMT

Then I Guess that this filename also is a typo? DNI_Daily_RICA_YYYYMMDD

DNI_DAILY_RICA_201604*.txt

Re: File import problem

stevietm — Wed, 26 Apr 2017 12:06:23 GMT

Fixed but still the same problem. I dont understand the error as all 4 files are the same except that the date format in the file will differ, and still the others for 2016 works fine.

Re: File import problem

stevietm — Wed, 26 Apr 2017 12:07:34 GMT

Or i am just missing something small and stupid somewhere.

This is the load of one of the other files

Usage << TROPICAL_DAILY_RICA_20160429 70 950 120 Lines fetched

Usage << TROPICAL_DAILY_RICA_20160430 70 952 033 Lines fetched

Re: File import problem

stabben23 — Wed, 26 Apr 2017 12:17:11 GMT

try:

FROM

[..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_'$(vReportMonth2016)'*.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq);

add ' around the variable '$(vReportMonth2016)'

Re: File import problem

stevietm — Wed, 26 Apr 2017 12:29:22 GMT

Nope, but thank for the help. I think i will exclude this for now.

Re: File import problem

pieterkup — Thu, 30 May 2019 06:41:48 GMT

Hi,

I know this post is a bit old. But I have another option for you to look into - especially if the Date format of the files differ.

What you could do is to load the files in a loop.

Start by opening a loop:

for Each zFile in FileList('..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_*.txt')

Now you have a variable (zFile) that contains the correct filename. Then load the data with the normal script but with: FROM ['$(zFile)'] to use the variable for the filename. The whole path should be included in the variable.

End the loop with:

next zFile;

This should solve the problems with the format of dates that differs from file to file.

Regards

Pieter