Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sandrapinto
New Contributor II

Left Join

Hi All

I have 2 tables and I need to take some data from One table - Table A - to the other - Table B.

Table A contains Employee ID's, Names and Types.

Table B contains Employee ID's ,Labor Costs and Labor Hours.

I would like to use table A to bring the Names and Types next to each Employee ID in Table B.

Left join brings all the Employee ID's, so I am getting in Table B empty lines for Employee ID's which exist in Table A but donwt exists in Table B. How can I avoid these empty lines?

In other words, I would like to use something similar to Vlookup in excel.

I know I can use Mapping Load but this clause allows only one filed for each Mapping Load, right?

Also, using Table A as a dimension is not useful to me because of other data in the script. That's why I need to bring the data into Table B rather than use a dimension to create the link between the two tables.

Thank you,

Sandra

Table A:

EmplyeeIDNameEmployeeType
1AAAtype 1
2BBBtype 1
3CCCtype 1
4GGGtype 1
5HHHtype 1
7KKKtype 2
8WWWtype 2
9XXXtype 2
10YYYtype 2
11MMMtype 2
12NNNtype 2
13DDDtype 2
14EEEtype 2

Table B:

EmployeeIDPaidHoursLaborCost
1221.159,335
2197.8016,946
3197.8018,231
4197.8015,001
533.412,529
6195.5314,799
7125.9011,205
8228.3810,922
9219.439,908
10274.0318,449
11200.808,837
Tags (1)
1 Solution

Accepted Solutions
niclaz79
Contributor III

Re: Left Join

Hi Sandra,

I did a

left join(TableB)

LOAD

     *

Resident TableA;

drop TableA;

And this was the result:

Result.png

Was this not what you wanted?

11 Replies
MVP
MVP

Re: Left Join

Like this?

MAP_TableA_NAME:

Mapping LOAD EmployeeID,

  Name

FROM TableA;

MAP_TableA_TYPE:

Mapping LOAD EmployeeID,

  EmplyeeType

FROM TableA;

TableB:

LOAD

  EmployeeID,

  ApplyMap('MAP_TableA_NAME', EmployeeID, 'Unknown') as EmployeeName,

  ApplyMap('MAP_TableA_TYPE', EmployeeID, 'Unknown') as EmployeeType,

  PaidHours,

  LaborCost

FROM TableB;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
niclaz79
Contributor III

Re: Left Join

Hi Sandra,

I did a

left join(TableB)

LOAD

     *

Resident TableA;

drop TableA;

And this was the result:

Result.png

Was this not what you wanted?

sandrapinto
New Contributor II

Re: Left Join

Thank you Jonathan. This is what I have now in my current script, but I thought some Join Clause would be easier. That is to say, using 2 Apply Maps gets the work done, but is there another, simpler way?

Thanks anyway

hacr-sds
Contributor

Re: Left Join

You can read more about the options here: https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/combine-tables-join...

And I think this topic can answer your questions about when to use Joins Joins and Lookups

siddharth_s3
Contributor

Re: Left Join

You ideally don't have to do mapping load twice. Try with mapping load and subfield

Try this instead:

MappingTable:

Mapping LOAD EmployeeID,

  Name & '-' & EmplyeeType

FROM TableA;

Main:

LOAD

  EmployeeID,

  Subfield(ApplyMap('MappingTable', EmployeeID, 'NA'),'-',1) as EmployeeName,

  Subfield(ApplyMap('MappingTable', EmployeeID, 'NA'),'-',2) as EmployeeType,

  PaidHours,

  LaborCost

FROM TableB;

sandrapinto
New Contributor II

Re: Left Join

Yes. Now I see that  my mistake was that I did left join (TableA) instead of B. Thanks a lot!

Sandra

niclaz79
Contributor III

Re: Left Join

Great! If you can mark my answer as correct I would be grateful as well!

niclaz79
Contributor III

Re: Left Join

left join(TableB)

LOAD

     *

Resident TableA;

drop TableA;

No mapping needed in this case.

siddharth_s3
Contributor

Re: Left Join

True,

I avoid joins if there are below 5 columns I have to map.

Joins can create duplicate data especially if data is coming from Excels, it is dangerous as calculations might go wrong.

I use joins when I have more than 5 columns to join, else subfield and mapping load.

There are many articles which say applymap is faster as well

Community Browser