Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 3 tables Tab1, Tab2
Tab1 structure as follows:
Project Number | Project Name | Type Code | Rate | Period | Data |
1 | A | CNT | 1500 | Jan-13 | 1 |
2 | B | PMT | 1500 | Feb-13 | 2 |
3 | C | PMT | 2500 | Mar-13 | 3 |
4 | D | CNT | 2700 | Apr-13 | 4 |
Tab2 Structure as Follows:
Project Number | Project Name | Type Code | Rate | Period | Data |
1 | A | CNT | 1500 | Jan-13 | 1 |
2 | B | PMT | 1500 | Feb-13 | 2 |
3 | C | PMT | 2500 | Mar-13 | 3 |
5 | E | CNT | 1500 | May-13 | 5 |
6 | F | PMT | 2700 | Jun-13 |
|
7 | G | CNT | 3200 | Jul-13 |
|
8 | H | PMT | 2500 | Aug-13 |
|
My requirement is I want to add the records which are not available in Tab1,
Table should be as follows
Project Number | Project Name | Type Code | Rate | Period | Data |
1 | A | CNT | 1500 | Jan-13 | 1 |
2 | B | PMT | 1500 | Feb-13 | 2 |
3 | C | PMT | 2500 | Mar-13 | 3 |
4 | D | CNT | 2700 | Apr-13 | 4 |
5 | E | CNT | 1500 | May-13 | 5 |
6 | F | PMT | 2700 | Jun-13 |
|
7 | G | CNT | 3200 | Jul-13 |
|
8 | H | PMT | 2500 | Aug-13 |
|
I used concatenation and joins but I’m getting error.
Please someone have a look into this, give me suggestion.
Thanks,
Chiru
See the attached dummy app
Try like:
Final:
Load * from Tab2;
Load * from Tab1 Where Not Exists([Project Number]);
Try:
[Project]:
LOAD Project Number,
Project Name,
Type Code,
Rate,
Period,
Data
FROM Tab1
CONCATENATE(Project)
LOAD Project Number,
Project Name,
Type Code,
Rate,
Period,
Data
FROM Tab2
WHERE NOT EXISTS (Project Number)
Chiru,
Contatenation is what you need. What the error is?
BTW, when loading the 2nd table, use condition where not exists("Project Number") if you want to avoid duplicates.
Regards,
Michael
Hi
The best way is to concatenate the tables and then perform a distinct load; Therefore;
Load
*
From Table1;
Concatenate (Table1) Load
*
From Table1;
FinalTable
NoConcatenate Load Distinct
*
Resident Table1;
Drop Table1;
Let me know if this is correct. I hope I helped.
Regards
Steve
Hi
This should do it:
Concatenate
LOAD [Project Number],
[Project Name',
...
FROM Table2
Where Not Exists([Project Number]);
Regards
Jonathan
Concatenate from both tables and afterwards a distinct load from these table will work. Also you could build a unique key from your fields and use this key in a where-clause.
- Marcus
Hi Steve,
Thanks for your Reply, it's working fine now.
Thanks,
Chiru