Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks for response,
I have created one sample app for your help, same is attached
App which is working fine. I have removed the Left join and data for Sal2018 is also linking.
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
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.
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