topic Re: peek( or lookup( function help (unsure which) in QlikView

peek( or lookup( function help (unsure which)

Mon, 18 Mar 2013 16:42:23 GMT

hi,

I have a tables such as:

TABLE A:

Postcode | Longitude | Latitude

AA1 AAA 123456 123456

AA2 AAA 456789 789789

AA3 AAA 456456 123123

and:

TABLE B:

Postcode

AA1 AAA

AA2 AAA

AA3 AAA

My load script looks like this

TABLE A:

SQL SELECT

Postcode, Longitude, Latitude

TABLE B: SQL SELECT

Postcode

How would I modify my load script, to load TABLE B with the Longitude and Latitude taken from table A?

Have I made myself clear?

Thanks.

Re: peek( or lookup( function help (unsure which)

Anonymous — Mon, 18 Mar 2013 16:47:36 GMT

I wonder why you need table B if table A has everything, but the right answer is: JOIN.

Regards,

Michael

Re: peek( or lookup( function help (unsure which)

Anonymous — Mon, 18 Mar 2013 16:48:25 GMT

Hi,

Not sure I understand the issue. The way you have the script at the moment, QlikView will "join" the data for you (you will see the join in your data model). If you want to "merge" the tables in to one, then you need to physically join them... you could try a simple LEFT JOIN.....

TABLE B:

Load Postcode

SQL SELECT

Postcode

Left Join

TABLE A:

LOAD Postcode,

Longitude,

Latitude

SQL SELECT

Postcode, Longitude, Latitude

This will create one table (TABLE B) with 3 columns, with only Long and Lats from TABLE A where there is a Postcode in TABLE B.

Cheers.

Re: peek( or lookup( function help (unsure which)

Mon, 18 Mar 2013 17:01:34 GMT

I still need the data in two tables at the end though. Due to doing some comparisons on long/lats. How do I use JOIN to accomplish this?

Re: peek( or lookup( function help (unsure which)

Anonymous — Mon, 18 Mar 2013 17:08:39 GMT

If you want to kep the Long and Lats in seperate table of their own you do not need to do anythying. The fact that you have the Postcode column named the same in both tables, QlikView will "join" the data for you.

If you want to reduce the dimension (Long and Lat) table based on the postcodes in the postcode table, you can use a KEEP join.

TABLE B:

Load Postcode

SQL SELECT

Postcode

Left Keep

TABLE A:

LOAD Postcode,

Longitude,

Latitude

SQL SELECT

Postcode, Longitude, Latitude

You could also investigate the EXISTS clause.

Re: peek( or lookup( function help (unsure which)

smirkinaa — Mon, 08 Apr 2013 19:02:14 GMT

TABLE A:

SQL SELECT

Postcode, Longitude, Latitude

TABLE B: SQL SELECT

Postcode

SubstituteLongitude:

Load Mapping Postcode, Longitude

Resident [TABLE A]

SubstituteLatitude:

Load Mapping Postcode, Latitude

Resident [TABLE A]

[TABLE Bv1]:

Load

Postcode,

ApplyMap('SubstituteLongitude',Postcode) as LongitudeB,

ApplyMap('SubstituteLatitude',Postcode) as LatitudeB

Resident

[TABLE B]

Drop Table [TABLE B];

Rename Table [TABLE Bv1] To [TABLE B];