Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Ta
Joe
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
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
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
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
Thanks Miguel.
It was indeed very helpful.
Joe