JOIN and remove missing values in observations

[#1]

Dear All,

I have applied this simple script:

Table1:

LOAD * INLINE [
    a, value
    2, 10
    3,8.99];

Table2:
LOAD * INLINE [
    a, Response
    2, 'Correct'
    5, 'Incorrect'
    9, 'Correct'];

Table3:
LOAD  a, value
Resident Table1;
LEFT JOIN (Table1)  LOAD  a, Response
Resident Table2;

The idea is to combine both tables in order to get this kind of observations:

a     Response     value

2     Correct          10

3     -                      8.99

Observations with a = 5 and 9 are not included, they need to be removed from the list in teh JOIN statement.

However, I ahve the impression that the applied JOIN statement does not work well, because when programming further, a new table will not be created anymore.

Could you please check?

Appreciate your help very much,

Best regards,

Cornelis.

[bbi_mba_76]

Hi,

maybe with

drop table Table2;

?

[#3]

Hi,

Thank you for your prompt answer.

No, it will not help. When adding this script:

Drop table Table1,Table2;

Table4:
LOAD a, Response, value as New_value
Resident Table3 where value >0;

I get an error message: Table not Found

Any idea?

[#4]

Hi,

Use a NOCONCATENATE before the LOAD of Table3.

Your Table3 is automatically concatenate with Table1. 

[jagan]

Hi,

Try this

Temp:

LOAD * INLINE [
    a, value
    2, 10
    3,8.99];

LEFT JOIN(Temp)

LOAD * INLINE [
    a, Response
    2, 'Correct'
    5, 'Incorrect'
    9, 'Correct'];

Data:

NoConcatenate

LOAD

*

RESIDENT Temp

WHERE Not IsNull(value);

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

[nilesh_gangurde · Accepted solution]

Table3:
LOAD  a, value
Resident Table1;
LEFT JOIN (Table1)  LOAD  a, Response
Resident Table2;

You need to change the script like this:

Table3:
LOAD  a, value
Resident Table1;
LEFT JOIN (Table3)  LOAD  a, Response
Resident Table2;

Drop table Table1;

Drop table Table2;

-Nilesh

[#7]

Hello Marie-Sophie,

Yes, that is a good remark in combination with other suggestion.

Thank you for your quick answer.

Best regards,

Cornelis

[#8]

Hi Jagan,

Happy to see that there is an alternative!

Thanks for it, it will contribute to a broader view in scripting.

I have changed the script and it works.

Best regards,

Cornelis.

[#9]

Hi,

Almost.. almost...!

In combination with other suggestions.

Thanks!

Best regards,

Cornelis.