topic Re: help in QlikView

help

userid128223 — Wed, 23 Sep 2015 19:50:49 GMT

Table A -- Has mix of 3 & 6 digit zip codes

code	City
ABC123	Name1
ABD321	Name2
ABE	Name3
ABC	Name4
ABD	Name5

Table B -- has all 6 digit

Code	City
ABC123	Name1
ABD323	Name7
ABE456	Name3
ABC784	Name4
ABD223	Name5

Need Solution:

I need to do inner join between A & B where first all 6 digit are matched once match are found you filter them from second inner join where match will be performed on first 3 digit code. final results will be combination of both matches.

Re: help

pamaxeed — Wed, 23 Sep 2015 20:23:41 GMT

So in this example the output is ABC123 ....after the two inn er joins....

Re: help

userid128223 — Wed, 23 Sep 2015 20:25:12 GMT

No. Because at 6 digit that will be the match, but another inner join match needs to be perfromed excluding the match. now at first 3 digit level.

Re: help

userid128223 — Wed, 23 Sep 2015 20:29:11 GMT

Results

code	City
ABC123	Name1

ABE	Name3
ABC	Name4
ABD	Name5

Re: help

pamaxeed — Wed, 23 Sep 2015 20:53:20 GMT

See the sample attached.

It should work, the output is the one u indicated.

Cheers,

Patric

Re: help

userid128223 — Wed, 23 Sep 2015 21:03:02 GMT

Thanks Patric, i don't see any code in the script.

Re: help

pamaxeed — Wed, 23 Sep 2015 21:07:20 GMT

Sry here you are .

Re: help

userid128223 — Wed, 23 Sep 2015 21:14:52 GMT

can you please explain the code. thx

Re: help

pamaxeed — Wed, 23 Sep 2015 21:31:13 GMT

It is not complicated:

First inner join on code and city
Second inner join on the first 3 characters of code and city excluding the codes i have got as output from the first inner join, see the WHERE NOT EXISTS part
After that i concatenate the 2 output tables in one result table, and drop the two other tables.

Hope it helped u. Plz mark the answer as correct if it covered ur requirements

Re: help

userid128223 — Wed, 23 Sep 2015 21:41:20 GMT

Thx.

What does 2 parameters do in Where Not Exist:

Where Not Exists(RefCode, Code);

Re: help

pamaxeed — Wed, 23 Sep 2015 21:47:17 GMT

Refcode is the field created previously in the script (see first inner join part)...it is simply comparing this field with the code field of the current load statement and negate it if the condition is met.

Plz refer to the Qlikview Help...

Regards,

Patric

Re: help

userid128223 — Wed, 23 Sep 2015 21:56:14 GMT

thx. got it. appreciate your help.