JOIN - how to avoid the blank line in fields from the joined table?

[datanibbler]

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

[jonathandienst]

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?

[Gysbert_Wassenaar]

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.

[datanibbler]

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.

[datanibbler]

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 ...

[Anonymous]

Try something like this in your load script :

     if ( len ( trim (YourField) ) > 0 , YourField , null() ) as YourField

[HirisH_V7]

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

[datanibbler]

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.

[HirisH_V7]

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

[datanibbler · Accepted solution]

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