Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sebdupont
New Contributor

partial left join

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

CountryCity
USAMiami
FRANCEParis
USADallas
SPAINMadrid

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

TABLE2

citylanguage
LyonFrench
New YorkEnglish

Result :

TABLE1

CountryCityLanguage
USAMiami
FRANCEParis
USADallas
SPAINMadrid
LyonFrench
New YorkEnglish

The third table to complete partial column Country

TABLE 3

CountryCity
FRANCELyon
USANew York
USAAtlanta
USALos Angeles

Expected résult with a left join

TABLE1

CountryCityLanguage
USAMiami
FRANCEParis
USADallas
SPAINMadrid
FRANCELyonFrench
USANew YorkEnglish

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 ?

6 Replies
sebdupont
New Contributor

Re: partial left join

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

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;

Capture.PNG

sebdupont
New Contributor

Re: partial left join

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

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
New Contributor

Re: partial left join

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

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

Community Browser