topic Re: JOIN and remove missing values in observations in QlikView

JOIN and remove missing values in observations

Fri, 25 Oct 2013 07:40:53 GMT

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.

Re: JOIN and remove missing values in observations

bbi_mba_76 — Fri, 25 Oct 2013 07:47:51 GMT

Hi,

maybe with

drop table Table2;

Re: JOIN and remove missing values in observations

Fri, 25 Oct 2013 07:58:45 GMT

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?

Re: JOIN and remove missing values in observations

Fri, 25 Oct 2013 08:03:22 GMT

Hi,

Use a NOCONCATENATE before the LOAD of Table3.

Your Table3 is automatically concatenate with Table1.

Re: JOIN and remove missing values in observations

jagan — Fri, 25 Oct 2013 08:04:21 GMT

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.

Re: JOIN and remove missing values in observations

nilesh_gangurde — Fri, 25 Oct 2013 08:16:11 GMT

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

Re: JOIN and remove missing values in observations

Fri, 25 Oct 2013 08:24:12 GMT

Hello Marie-Sophie,

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

Thank you for your quick answer.

Best regards,

Cornelis

Re: JOIN and remove missing values in observations

Fri, 25 Oct 2013 08:25:53 GMT

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.

Re: JOIN and remove missing values in observations

Fri, 25 Oct 2013 08:29:05 GMT

Hi,

Almost.. almost...!

In combination with other suggestions.

Thanks!

Best regards,

Cornelis.