topic Re: load qvd and resident table in QlikView

load qvd and resident table

yelin_nyu — Mon, 11 Mar 2013 03:26:12 GMT

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?

Re: load qvd and resident table

er_mohit — Mon, 11 Mar 2013 05:40:08 GMT

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

Re: load qvd and resident table

Mon, 11 Mar 2013 05:56:05 GMT

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;

Re: load qvd and resident table

yelin_nyu — Mon, 11 Mar 2013 06:23:31 GMT

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.

Re: load qvd and resident table

kaushiknsolanki — Mon, 11 Mar 2013 06:28:17 GMT

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

Re: load qvd and resident table

yelin_nyu — Mon, 11 Mar 2013 06:28:55 GMT

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

Re: load qvd and resident table

yelin_nyu — Mon, 11 Mar 2013 06:37:24 GMT

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.

Re: load qvd and resident table

kaushiknsolanki — Mon, 11 Mar 2013 06:53:11 GMT

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

Re: load qvd and resident table

yelin_nyu — Mon, 11 Mar 2013 07:14:30 GMT

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