Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator III

Why are these records loading

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_NumStartInvAmtInvDateTypeTtlInv
31/12/2099 31/12/2099BkVal

I don't want these records to appear.

What am I doing wrong?

Ta

Joe

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

4 Replies
Miguel_Angel_Baeyens

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

mazacini
Creator III
Creator III
Author

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

Miguel_Angel_Baeyens

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

mazacini
Creator III
Creator III
Author

Thanks Miguel.

It was indeed very helpful.

Joe