Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

peek( or lookup( function help (unsure which)

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.

5 Replies
Highlighted
Champion III
Champion III

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

Regards,

Michael

Highlighted
Creator
Creator

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.

Highlighted
Not applicable

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?

Highlighted
Creator
Creator

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.

Highlighted
Contributor III
Contributor III

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];