Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I wonder why you need table B if table A has everything, but the right answer is: JOIN.
Regards,
Michael
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.
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?
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.
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];