Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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;
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.
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
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
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.
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
ok i fixed my problem, it turns out i need to put noconcatenate in front of the 'final_tbl' name.... interesting.