Adding records

[#1]

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	6.1
7	G	CNT	3200	Jul-13	6.3
8	H	PMT	2500	Aug-13	7.2

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	6.1
7	G	CNT	3200	Jul-13	6.3
8	H	PMT	2500	Aug-13	7.2

I used concatenation and joins but I’m getting error.

Please someone have a look into this, give me suggestion.

Thanks,

Chiru

[er_mohit]

See the attached dummy app

[tresesco]

Try like:

Final:

Load * from Tab2;

Load * from Tab1 Where Not Exists([Project Number]);

[#4]

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)

[Anonymous]

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

[#6]

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

[jonathandienst]

Hi

This should do it:

Concatenate

LOAD [Project Number],

     [Project Name',

     ...

FROM Table2

Where Not Exists([Project Number]);

Regards

Jonathan

[marcus_sommer]

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

[#9]

Hi Steve,

Thanks for your Reply, it's working fine now.

Thanks,

Chiru