Issue with Concatenation

[#1]

Hi Experts,

I have an issue with the concatenation. below is the script i am using.

Table1:

LOAD

     if(isnull([Sr. Num])=-1 or [Sr. Num]='' or [Sr. Num]='#',[key1] & [Country] , [key1) as [key1],

     [Year],

     [Month],

     [Country]

from QVD1;

Concatenate(Table2):

Load

[key1],

[key1] as [key1_new],

[Month]

[Year]

resident Table1

where not exists ([key1_new],[key1]);

After the concatenation,[ key1] and [key1_new] columns should have the same value. But for one of the records, i see only [key1] is populated but [key1_new] is showing null.

Please suggest if i am missing anything here to have the same value populating for both columns?

Thanks,

Vamsi Krishna.

[sunny_talwar]

But you resident load will bring 0 rows, because you are doing a resident load from Table1 itself. What are you trying to do?

[adamdavi3s]

Just to be clear that you're using key_1 to link table1 and table2

Table 2 has already been loaded

Then you load table 1

Then you're trying to load any keys in table1 that don't exist in table2, into table2?

[jonathandienst]

You are missing a closing ] after key1 in:

if(isnull([Sr. Num])=-1 or [Sr. Num]='' or [Sr. Num]='#',[key1] & [Country] , [key1) as [key1],

if(isnull([Sr. Num])=-1 or [Sr. Num]='' or [Sr. Num]='#',[key1] & [Country] , [key1]) as [key1],

or is that just a cut & paste error?

[#5]

Yes,

First table1 and table2 are loaded. later we are concatenating the table1 with table2.

If i query in the qvd1 (table1), I have 4 records for a particular transaction. (Invoice ID= 1,2,3,and #). After the concatenation as specified in the original post, I am seeing only 3 records (Invoice Id= 1,2,3) but the fourth record with invoice id as # is missing. becase key2_new is populating as null. (key2 has value)

When i filter as 'Where not exists (key2_new, key2) then 4th record is getting dropped.

My question here is why is the key2_new is populating as null for the 4th record only.

Thanks,

Vamsi Krishna.

[#6]

Jonathan,

Yes, it is just a copy paste issue.

Thanks,

Vamsi Krishna.