Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
this is rather a question of look and layout of the app.:
- I have a list of items like 101, 102, 201, 202 etc.
- I have a much shorter list of items where goods for some other place are stored (out of the first list, so all items match)
=> How can I avoid that blank line in the listbox that I naturally get after a JOIN (normal JOIN for now) in a listbox of that second much
shorter list?
Thanks a lot!Best regards,
DataNibbler
Hi,
this is solved.
I changed the order of the script_tabs so that the JOIN is at the very end and I implemented that trick with LEN() to catch NULL values and now that line is gone.
Thanks a lot!
Best regards,
DataNibbler
A normal join in QV is a full outer join. But I am not sure what you mean with the blank lines. What is displaying in the listbox? Where is the blank line coming from?
Nulls will not be shown in a listbox. If you have a blank line in a listbox then you have a real value. It could be an empty string or a string with one or more spaces. You can test for that with Len(Trim(Item)) if you want to remove those.
Hi Jonathan,
Quite simply, that blank line showing up in the listbox corresponds to all the items in my full list which do not figure in the second much shorter list.
I have already tried with a LEFT JOIN, but that does not seem to work - well, it works fine, but the blank line is still there.
The Debugger tells me there are only 5 records in that short list which is correct.
Hi Gysbert,
TRIM() is always a good idea. Still, the blank line remains´, even with the respective WHERE clause in place.
Strangely, that item has a LEN() of 0, but it is recognized as a text ...
Try something like this in your load script :
if ( len ( trim (YourField) ) > 0 , YourField , null() ) as YourField
Hi,
May be like this,
Data:
LOAD IF(IsNull(ID ),0) as ID,
or
IF(IsNull(ID ),Null()) as ID,
[Sales Order ID],
ShipDate as Date,
Sales
FROM
[Sales Orders.xls]
(biff, embedded labels, table is [Sales Orders$]) Where ID <> Null();
This way we can exclude null records at back end.
HTH,
Hirish
I think I know what the reason is - it is a question of the order in which I load different tables: After I do this JOIN between my list of shelves and that short list of shelves_with_goods_for_other_plants, I concatenate some other tables to my list of shelves.
I guess when I change the order and do this JOIN at the very end, then I could get rid of this.
Hi,
It will be based on your tables ,
Your master Table:
ID
Fields
Left Join
Table 1:
ID,
Field1
Left Join
Table 2:
ID,
Field2
Left Join
Table 3:
ID,
Field3
Another Way may be,
Table11:
LOAD * INLINE
[
A, B, C
1, 1, 1
2, 2, 2
3, 3, 3
];
Concatenate
Table22:
LOAD * INLINE
[
B, C, D
2, 2, 2
3, 3, 3
5, 5, 5
];
HTH,
Hirish
Hi,
this is solved.
I changed the order of the script_tabs so that the JOIN is at the very end and I implemented that trick with LEN() to catch NULL values and now that line is gone.
Thanks a lot!
Best regards,
DataNibbler