load qvd and resident table

[yelin_nyu]

Hi, still pretty new to qlikview and need someone who can tell me the best way to the joins

main:

load key,

field

from 123.qvd (qvd);

left join (main)

load key,

value

from 456.qvd (qvd);

final_tbl:

noconcatinate load

key,

field,

value

resident main

where not match(value, '1', '2');

I will get no values at all in final tbl. Why?

But if i do this:

load main-->left join the value --> store main into a qvd and drop it --> pull that qvd again in final_tbl and do the where statement

I will get the results i what. how is these 2 methods different?

[er_mohit]

try this

where not exists(value=1 or value=2);

or in your syntax might be remove the single quote in match function

where not match(value,1,2);

hope it helps

[#3]

main:

LOAD * INLINE [

    key, field

    1, a

    2, b

    3, c

];

Join(main)

LOAD * INLINE [

    key, value

    4, 1

    5, 2

    6, 5

];

final_tbl:

NoConcatenate

load

key,

field,

value

resident main

where not match(value, '1', '2');

DROP Table main;

[yelin_nyu]

Thanks for the input guys, I don't think the problem is the where statement. because when i take out where statement, my value is still blank. meaning from main to final_tbl, only key and field survived, but value didn't get in.

[kaushiknsolanki]

Hi,

    Try below script.

main:

LOAD * INLINE [

    key, field

    1, a

    2, b

    3, c

];

Join(main)

LOAD * INLINE [

    key, value

    4, 1

    5, 2

    6, 5

];

final_tbl:

load

key as KEY,

field,

value

resident main

where not match(value, '1', '2');

DROP Table main;

Regards,

Kaushik Solanki

[yelin_nyu]

im trying to left join the table with values to main table. they have common keys. i actually couldn't figure out how is this different from what i had

[yelin_nyu]

so if i don't rename my key, i can't use resident table? i rather not rename my key if i don't have to. i find this kind of silly and trying to understand the logic behind this.

i used noconcatenate without where statement meaning i just want to see if it will pull in anything, but it doesn't. so everyone can ignore where statement for now, and focus on why it's not getting 'value' while using noconcatenate.

[kaushiknsolanki]

Dear Yelin.

     This is not silly at all.

     The logic behind renaming field is that, in QlikView if both tables have same fields it concatenates automatically and thus i suggested you to use the renaming of field instead of noconcatenate.

     But anyway's your noconcatenate script is giving proper values.

Regards,

Kaushik Solanki    

[yelin_nyu]

ok i fixed my problem, it turns out i need to put noconcatenate in front of the 'final_tbl' name.... interesting.