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

[PrashantSangle]

Hi,

After that script

Write

Final_table1:

Load

      month,

      itemnumber,

      week,

      sellingprice

resident table1

Drop table table1;

Hope this will help.

Regards

[martynlloyd]

Extending Max's idea, load Table1 into memory and then use LEFT JOIN to join the tables

FinalTable

LOAD

...

Resident Table3

;

Left Join (FinalTable)

...

Resident Table1

;

Drop Table1;

[#4]

I tried this step but the values coming from table1 (excel table) are nulls. So the table contains '-' hypen.

Any help here?

[#5]

Just wanted to update you guys, I am still nowhere with this,

also I tried doing an inner join to see if I could get hold of just the common values, but to my surprise it returned nothing, Zero rows.. so this makes me believe that qlikview is thinking that all the columns are different though they have the same name.

Have you guys come across this?

[maxgro]

if you do an inner join (join on 4 fields) you get 0 records because sellingprice is different (100 or 0)

excel_data:

load * inline [

  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:

inner join (excel_data)

load * inline [

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

];

if you do a left join (field month, itemnumber, week, not sellingprice)

excel_data:

load * inline [

  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:

left join (excel_data)

load * inline [

month, itemnumber, week, sellingprice2

  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

]; 

you get

if you change left to right join, you get 7 records

[#7]

Hi correct me if i wrong
1) it seems like all the column names are same it's generates synthetic keys

2) so rename(as) all the columns
3) To avoid null use Right keep
     it maintain the data in individual tables
4) next do CONCATENATE (Table1) LOAD * RESIDENT Table2;

Reference link

[martynlloyd]

Hi Pragati,

You seem to be having some trouble with joins, I'm wondering if you need a different approach - maybe concatenate would work better in this situation, it would enable you to control things easier with IF logic:

table1temp:

load
      month & itemnumber & week as T1Key,
      month,
      itemnumber,
      week,

      sellingprice as T1price

from excel file

Concatenate
load

      'T3' as Type,

      month & itemnumber & week as T3Key,
      month,
      itemnumber,
      week,

      sellingprice as T3price

resident table3

Table1:
Load

IF(.....     .....) as ....

Resident table1temp
Where .....

BTW it is still not clear what you are trying to achieve - are there more fields that you are not telling us about - if you simply want to add the price, use

load

      month & itemnumber & week as T3Key,
      month,
      itemnumber,
      week,

      sellingprice

resident table3

CONCATENATE

load
      month,
      itemnumber,
      week,

      sellingprice

from excel file

Where exists(T3Key,  month & itemnumber & week)

[martynlloyd]

BTW Pragati,

You did not mark your thread as a Question, so we don't get any points for helping you...

[#10]

Hi,

Thanks for the heads up, I have opened this as a question now, with more information as to what I am trying to achieve.

Please have a look at that.

Problems joining two exactly same tables

Thanks