partial left join

[sebdupont]

Hi,

I try to do something, but i don't know if it's possible.

I need your help.

My exemple is very simplistic, but in reality, my datas are much complexe and i must load them in the exemple order.

First table : TABLE1

Country	City
USA	Miami
FRANCE	Paris
USA	Dallas
SPAIN	Madrid

Second table join to the first table with concatenate (TABLE1)

TABLE2

city	language
Lyon	French
New York	English

Result :

TABLE1

Country	City	Language
USA	Miami
FRANCE	Paris
USA	Dallas
SPAIN	Madrid
	Lyon	French
	New York	English

The third table to complete partial column Country

TABLE 3

Country	City
FRANCE	Lyon
USA	New York
USA	Atlanta
USA	Los Angeles

Expected résult with a left join

TABLE1

Country	City	Language
USA	Miami
FRANCE	Paris
USA	Dallas
SPAIN	Madrid
FRANCE	Lyon	French
USA	New York	English

In sql, i should do something with where clause : where table1.country is null or in join statement table1.country=table3.country and table1.country is null.

How to do something similar in Qlikview ?

[sebdupont]

I have an issue by adding a new field in left join, drop the older and rename new field.

Left join (Table1)

city,

Country as Country_temp

From Table3;

Drop field Country from Table1;

Rename Country_temp to Country;

If you have another solution !?!

[sunny_talwar]

May be using ApplyMap with Mapping load

TABLE3:

Mapping

LOAD City,

  Country;

LOAD * INLINE [

    Country, City

    FRANCE, Lyon

    USA, New York

    USA, Atlanta

    USA, Los Angeles

];

TABLE1:

LOAD * INLINE [

    Country, City

    USA, Miami

    FRANCE, Paris

    USA, Dallas

    SPAIN, Madrid

];

Concatenate(TABLE1)

LOAD * INLINE [

    City, Language

    Lyon, French

    New York, English

];

FinalTable:

NoConcatenate

LOAD If(Len(Trim(Country)) = 0, ApplyMap('TABLE3', City), Country) as Country,

  City,

  Language

Resident TABLE1;

DROP Table TABLE1;

[sebdupont]

Thanks Sunny

It's a possible solution for my problem but, as i have a lot of data, reload all data several times will slow down too much my global load.

[sunny_talwar]

I am not sure what extra load are you seeing above? I mean I have a final load, but you mentioned several times? I am not sure what you mean when you say reload all data several times?

[sebdupont]

sorry, i was not clear.

This manipulation comes back several times in my script for different value.

I must proceed with intermediate steps to complete my data, so reload several times the totality of the data it's not possible.

[sunny_talwar]

Unless you show your script of what you have, I am having a hard time picturing what you have and why you end up with several manipulative resident loads. I really want to help, but I won't be without more information