Join not giving the desired results

[#1]

Hi,

I am trying to join two tables, one is loaded from an excel file and other is a resident.

the table structure and data looks like the this:

table1:

load

      month,

      itemnumber,

      week,

      sellingprice

from excel file

right join (table1)

load

      month,

      itemnumber,

      week,

       sellingprice

resident table3

I need all the rows from table3 and the equivalent values from table1.

with right join it contains all the rows from table3 but not the values.

Can you please help me or give me any idea, as to where I could be going wrong?#

Thanks

[m_woolf]

I would expect that two tables would be joined on a common field.

You have 4 common fields.

[PrashantSangle]

Hi,

Can you post sample data.

As you doing right join on the basis of 4 common field.

So it will gives you only those data which are present in resident table and corresponding value from table1

Which must give correct values

Check it and if possible share sample apps.

Regards

[jaimeaguilar]

Hi,

I think that the problem is that all the fields are the same, so the join won't work. It sounds to me like you could achieve the result you want if you concatenate instead of joining. However, It would be better if you could post some sample file,

regards

[#5]

Thanks guys.. I just checked and I reckon the data types of the common fields is different.

Will try and change that and see how it goes.. will keep this post updated.

Thanks

[maxgro]

may be you only need to join the first 3 fields? If so, rename sellingprice

table1:

load

      month,

      itemnumber,

      week,

      sellingprice as sellingpricetable1

from excel file

[#7]

Hmm.. the data type change did not help.

here is some sample data

excel data

  month, itemnumber, week, sellingprice

  Aug - 2014, 123, Week 31 - Aug, 100

  Aug - 2014, 123, Week 32 - Aug, 100

  Aug - 2014, 123, Week 33 - Aug, 100

  Aug - 2014, 123, Week 34 - Aug, 100

  Aug - 2014, 123, Week 35 - Aug, 100

resident table

month, itemnumber, week, sellingprice

  Aug - 2014, 123, Week 31 - Aug, 0

  Aug - 2014, 123, Week 32 - Aug, 0

  Aug - 2014, 123, Week 33 - Aug, 0

  Aug - 2014, 123, Week 34 - Aug, 0

  Aug - 2014, 123, Week 35 - Aug, 0

  Aug - 2014, 1234, Week 31 - Aug, 0

  Aug - 2014, 1234, Week 32 - Aug, 0

so the difference is, there is value in SellingPrice column, and I want that in the resident data for itemnumber '123'

PS: Sellingprice is an inputfield.

Hope I am making some sense.

Thanks

[PrashantSangle]

Hi,

rename one of the Selling price from table

Try like this,

table1:

load

      month,

      itemnumber,

      week,

      sellingprice

from excel file

right join (table1)

load

      month,

      itemnumber,

      week,

       sellingprice as old_selllinPrice

resident table3

Regards,

[#9]

Thanks for your reply, I tried that, but then it gives me two entries one with zero values from resident table and others with proper values from excel table, I tried distinct but doesnt help.

I think its considering all the values as unique.. but how and why?

[#10]

Thanks for the reply, but then this would create two separate columns, which I dont want 😞