topic Re: partial left join in QlikView

partial left join

sebdupont — Fri, 02 Dec 2016 14:51:50 GMT

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 ?

Re: partial left join

sebdupont — Fri, 02 Dec 2016 15:40:21 GMT

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 !?!

Re: partial left join

sunny_talwar — Fri, 02 Dec 2016 16:27:07 GMT

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;

Re: partial left join

sebdupont — Mon, 05 Dec 2016 10:12:45 GMT

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.

Re: partial left join

sunny_talwar — Mon, 05 Dec 2016 11:00:28 GMT

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?

Re: partial left join

sebdupont — Mon, 05 Dec 2016 13:18:00 GMT

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.

Re: partial left join

sunny_talwar — Tue, 06 Dec 2016 02:03:29 GMT

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