topic Re: Why are these records loading in QlikView

Why are these records loading

mazacini — Mon, 30 Jul 2012 10:37:38 GMT

I have the following script:

LOAD Order_Num ,
     73050 as Start,
     73050 as InvDate,
    [Book Value] as InvAmt,
    [Book Value] as TtlInv,
     'BkVal' as Type
FROM
FILE

where [Book Value] <> 0 and len(Date) =0;

The idea is that I allocate the date 73050 (31/12/2099) in instances where there is a value in [Book Value] field but no Date in the Date field..

As it happens, there are no records with len(Date) = 0.

But the following records are appearing on my table:

Order_Num	Start	InvAmt	InvDate	Type	TtlInv
	31/12/2099		31/12/2099	BkVal

I don't want these records to appear.

What am I doing wrong?

Joe

Re: Why are these records loading

Miguel_Angel_Baeyens — Mon, 30 Jul 2012 10:42:55 GMT

Hi Joe,

Probably obvious but are you sure those "Date" values are null values (length = 0)? I mean, they might be blanks or some kind of spaces. The following should avoid that, removing blanks around the values in the cell:

WHERE [Book Value] <> 0 and Len(Trim(Date)) = 0;

Hope that helps.

Miguel

Re: Why are these records loading

mazacini — Mon, 30 Jul 2012 10:54:14 GMT

Hi Miguel

That didn't work for me - but it might be pointing toward my problem.

My source is a spreadsheet file. I think it might be loading these records for blank rows at the end of my data.

I have added a condition to my where clause:

where len(Order_Num)>0, and it seems to get around the problem.

But I suspect I may have records in the future without a value in Order_Num; and I WILL want to load these fields (but they will be excuded by tha above condition).

Is there a way to ignore blank rows at the end of my data in a spreadsheet?

Or is this my problem in the first place?

Regards

Joe

Re: Why are these records loading

Miguel_Angel_Baeyens — Mon, 30 Jul 2012 11:01:39 GMT

Hi Joe,

I'd say that there is no easy way to control that. Meaning that assuming that today you have 20 rows worth of data, plus four with blanks and another two with data as well, you would need to control that if the length of the whole row (summing up the Len(Trim()) of each column) equals to zero, then do not load them, otherwise, load them.

This would allow you to perform unattended reloads irrespective the number of rows within with all blanks.

You might try to load the spreadsheets using and ODBC driver, instead of the table files wizard in QlikView, and control there which fields are key and so which rows should be always loaded, regardless the number of Order_Num or so.

The best way to get that around is doint some cleanup in the source, if that's possible.

Hope that gives you a better idea.

Miguel

Re: Why are these records loading

mazacini — Mon, 30 Jul 2012 11:33:34 GMT

Thanks Miguel.

It was indeed very helpful.

Joe