topic validate csv files for completeness ? in QlikView

validate csv files for completeness ?

Thu, 03 Mar 2011 17:23:13 GMT

Hi Qlikview Community,

How can I ensure that when you read the csv Dateienn, Qlikview verified whether the data are complete ? [^o)]

For example, I receive for each day a separate csv file. I have a total of nearly 600's csv files.

....
..
25_02_2011_rep.csv
26_02_2011_rep.csv
27_02_2011_rep.csv
28_02_2011_rep.csv
01_03_2011_rep.csv
02_03_2011_rep.csv
..
..... etc

Unfortunately it happened to us again and again that our data provider fails [8o|] for technical reasons and we just this one day as shown in the above files, 26_02_2011_rep.csv miss.

How can I validate the csv files for completeness? For example, should miss a day, should this information be written into a variable. For example varMissing = '26_02_2011 '. Thank you!

Best regards,

validate csv files for completeness ?

matt_crowther — Thu, 03 Mar 2011 17:45:33 GMT

Attached is one of my solutions for a similar issue with QVD files.

It compares what's been loaded with what was expected and gives you a 'Missing Files' report.

Should point you in the right direction.

Hope that helps,

Matt - Visual Analytics Ltd

validate csv files for completeness ?

Tue, 19 Apr 2011 16:34:53 GMT

Hi,

thanks for the file. I was thinking more of a loop, something like this pseudocode:

x = 01.01.2008;
x = x + 1;
if (x not exists) { push x into a variable;}

In the end of this loop, i have a variable like this example: x = { 01.03.2008, 03.03.2008, 05.03.2008...}

Can somebody help me to realize this in that way ? Thank you !

validate csv files for completeness ?

Fri, 29 Apr 2011 14:57:34 GMT

Hi,

Here's a part of a solution that I was looking for. The next step is to check it for completeness. Any idea ?

Thank for any help !

Regards

LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar:

LOAD
TempDate AS CalendarDate,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());