Left Join not working properly with multiple files load using "*"

[rohitk1609]

Hi Techies,

I have a use case where I have two kinds of files.

1. Sales_2017, Sales_2018 and Sales_2019

2. Labour_2017,Labour_2018 and Labour_2019

Then I write the load script as:

Sales:

Load

*

from lib://$(Path)/Sales_20*.qvd]

left join

Sales:

Load

*

from lib://$(Path)/ Labour_20*.qvd]

The output is having data for 2017 Labour table only and skip  Labour_2018 and  Labour_2019.

If I remove  the left join then linking works fine for all three years fine but Left Join maps data for the first file of second table set.

My question is , is it a bug in Feb 2019 version ?

Thanks,

Rohit

[daanciorea]

Hi,
I think that the problem is your data. Can you attach some example of your data?

Dan.

[JordyWegman]

Hi Rohit,

What happens when you first load all the labour files and then left join this? And what is your key field for joining this? I would never suggest to left join with two load *..

Sales:
Load
*
from lib://$(Path)/Sales_20*.qvd]

 
Labour:
Load
*
from lib://$(Path)/ Labour_20*.qvd]

Left Join(Sales)
Load
 *
Resident Labour;

drop table Labour;

Jordy

Climber

[rohitk1609]

Thanks for response,

I have created one sample app for your help, same is attached

[rohitk1609]

App which is working fine. I have removed the Left join and data for Sal2018 is also linking.

[rohitk1609]

Hi Jordy,

When I run script without left join both years data for both set of table and years links with each other but when I run it with Lef join, table 1 from second data set(Labour) links with first data set Sales. Please have a look on the apps I attached in forms.

[marcus_sommer]

Usually joins couldn't be applied within loops because the join add fields to the first table and those fields became keys within the next iteration and quite probably they don't match for all of them. Therefore you find only the data from the first iteration. But if you follow the suggestion from Jordy it should work.

- Marcus

[Ivan_Bozov]

Your problem is similar to the one discussed here. Basically, the problem is caused by the wildcard load and the solution proposed by Jordy should work.

[rohitk1609]

Thanks for the note. Is it a feature of bug ?? Ideally it should load the first table set with * then put join with second data set ??

[marcus_sommer · Accepted solution]

I don't know if this process-order within the load-statement is mandatory to work properly respectively is more efficient in any way as the reversed way but I think it worked as designed. Personally I could imagine a more heavier load-statement with additionally options to specify the wished behaviour ... and there are other occasions, too in which it would have benefits, for example by creating mapping-tables.

- Marcus